How do I replace forumulas with the round function in one go

[Guest]

I am constantly working on spreadsheets and then realise I wish I had entered
the "round" function at the start. Instinctively I thought of Ctrl+H to
replace but can't figure out how you would put a ,0) at the end.
Also considered a macro with typing - F2, Home, =Round(,End, ,0) - but dont
know how to get this to work over a large range of formulas.
Any help would be appreciated

 

[Jim Rech]

I would suggest using the Precision as Displayed option available under
Tools, Options, Calculation. Make sure that all cells have the proper
number format before you set this workbook level setting. That is, if you
want a cell's value rounded to 2 decimal places for example, make sure that
that is how it is number formatted.

--
Jim
|I am constantly working on spreadsheets and then realise I wish I had
entered
| the "round" function at the start. Instinctively I thought of Ctrl+H to
| replace but can't figure out how you would put a ,0) at the end.
| Also considered a macro with typing - F2, Home, =Round(,End, ,0) - but
dont
| know how to get this to work over a large range of formulas.
| Any help would be appreciated

 

[Dana DeLouis]

... then realise I wish I had entered the "round" function

Also considered a macro ...but don't know how to get this
to work over a large range of formulas.

I have a toolbar button that runs the following code.
Adjust if you always want it to round to 0.

Sub Round_Add()
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =
'// Adds =ROUND( ) to Formulas in current Selection
'// May have more than one area selected
'// Avoids adding Round to beginning if already used
'// = = = = = = = = = = = = = = = = = = = = = = = = = = =

Dim BigRng As Range
Dim Rng As Range
Dim Cell As Range
Dim Equ As String
Dim iRound As Integer

On Error Resume Next
If Not (TypeOf Selection Is Range) Then Exit Sub
Set BigRng = Selection.SpecialCells(xlFormulas)
If BigRng Is Nothing Then Exit Sub

iRound = InputBox("Round to how many digits?", , 2)

Equ = "=Round(#,n_)"
Equ = Replace(Equ, "n_", iRound)

For Each Rng In BigRng.Areas
For Each Cell In Rng.Cells
If Not Cell.Formula Like "=ROUND(*" Then
Cell.Formula = Replace(Equ, "#", Mid$(Cell.Formula, 2))
End If
Next Cell
Next Rng
End Sub

 

[Guest]

Dana

That is perfect -

Thank you very much

Cheers

GusDubs