Replacing formula with round multiple times

[andyhofer]

Hi all

I have a spreadsheet that has around 12000 lines, in column D there is
a formula that creates a value such as 2000.123524. The value needs to
be exported to another application but I have to remove the decimal
places first.

How can I encase the current formula with the round function for all
the lines in one go? It would be a real chore to edit every line. I
can't change the top formula and copy it down as all the formula source
data comes from different places.

I know you can use replace but as I need to replace more than one thing
in the current formula I can't see that it is possible

Before example
=(A1/A2)
I need
=round(A1/A2,0)

Thanks all

 

[Gord Dibben]

Sub RoundAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=ROUND(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
cel.Value = "=ROUND(" & myStr & ",0)"
End If
End If
Next
End Sub

Select column D then run the macro.


Gord Dibben MS Excel MVP