using a macro round function

[Ganvenk]

I would like to create a shortcut for using ‘round’ function ( ctr + r ) in
the following manner:

A1 = 255.87 ; B1 = 45678.8974 ; C1 = +d5*25
If I apply Ctr + r on A1 it should become @round(255.87,0)
If I apply Ctr + r on B1 it should become @round(45678.8974,0)
If I apply Ctr + r on C1 it should become @round(+d5*25,0)

In lotus I used to use this as a macro on the following logic:
Go to cell
Apply ‘ctr+r’
The macro will do the following:
F2 (edit)
Home
Type @round(
End
Type ,0)
Enter

The same is not working in Excel. Can someone clarify or suggest
Thanks

[Bob Umlas]

This should do the trick:

Sub DoRound()
For Each oCell In Selection
oCell.Formula = "=ROUND(" & oCell.Formula & ",0)"
Next
End Sub


Bob Umlas
Excel MVP

"Ganvenk" <[email protected]> wrote in message
news:[email protected]...
> I would like to create a shortcut for using 'round' function ( ctr + r )
in
> the following manner:
>
> A1 = 255.87 ; B1 = 45678.8974 ; C1 = +d5*25
> If I apply Ctr + r on A1 it should become @round(255.87,0)
> If I apply Ctr + r on B1 it should become @round(45678.8974,0)
> If I apply Ctr + r on C1 it should become @round(+d5*25,0)
>
> In lotus I used to use this as a macro on the following logic:
> Go to cell
> Apply 'ctr+r'
> The macro will do the following:
> F2 (edit)
> Home
> Type @round(
> End
> Type ,0)
> Enter
>
> The same is not working in Excel. Can someone clarify or suggest
> Thanks
>

[Dave Peterson]

I don't think Bob saw the formula in C1:

Option Explicit
Sub DoRound2()
Dim oCell As Range
For Each oCell In Selection
If oCell.HasFormula Then
oCell.Formula = "=round(" & Mid(oCell.Formula, 2) & ",0)"
Else
oCell.Formula = "=ROUND(" & oCell.Formula & ",0)"
End If
Next
End Sub


Ganvenk wrote:
>
> I would like to create a shortcut for using ‘round’ function ( ctr + r ) in
> the following manner:
>
> A1 = 255.87 ; B1 = 45678.8974 ; C1 = +d5*25
> If I apply Ctr + r on A1 it should become @round(255.87,0)
> If I apply Ctr + r on B1 it should become @round(45678.8974,0)
> If I apply Ctr + r on C1 it should become @round(+d5*25,0)
>
> In lotus I used to use this as a macro on the following logic:
> Go to cell
> Apply ‘ctr+r’
> The macro will do the following:
> F2 (edit)
> Home
> Type @round(
> End
> Type ,0)
> Enter
>
> The same is not working in Excel. Can someone clarify or suggest
> Thanks

--

Dave Peterson