Replacing formula with round multiple times
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
On Thu, 17 Jan 2008 17:09:31 +0000, andyhofer
wrote:
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
|