Add ROUND function to formulas

[Steph]

I have a large data set that includes text, constants, and formulas. I would
like to add the ROUND function to each cell where a formula occurs (i.e.
round the value returned from each formula to zero decimal points). Can
anyone suggest a macro to do so?

Thank you.

 

[Jim Thomlinson]

Something like this should be close...

Sub AddRoundFunction()
Dim rngFormulas As Range
Dim rng As Range

On Error Resume Next
Set rngFormulas = UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not rngFormulas Is Nothing Then
For Each rng In rngFormulas
rng.Formula = "=round(" & Mid(rng.Formula, 2, 1024) & ", 0)"
Next rng
End If
End Sub

 

[Rick Rothstein]

Do you mean you want a macro that will be run one time and which will
permanently change your formulas so that they include the ROUND function
afterwards? If so, do **ALL** your formulas return numeric values (or do you
have some that perhaps return dates, text, etc.)? If not, are the formulas
that do return values confined to the same column or columns so they can be
selected or their range(s) specified?