PDA

View Full Version : round formula vba



SteveM99
09-21-2018, 08:15 AM
I need to create a macro that adds or replaces the round formula to cells selected which have either formulas in them or hard numbers.

I found this code but it does not take into account replacing a prior round formula. My round fomulas will always be the same and not nested within each other. I would also like to take out the zero bypass in this code which does not add the code if the cell vale is zero.

I am just not savy enough to figure out the code.

Sub Round_Formula_EREX()
Dim c As Range
Dim LResult As Integer
Dim leftstr As String
Dim strtemp As String
Set wSht1 = ActiveSheet
Dim straddress As Range
Dim iNum As Integer
Set straddress = Application.Selection
Set straddress = Application.InputBox("Range", xTitleId, straddress.Address, Type:=8)
iNum = Application.InputBox("Decimal", xTitleId, Type:=1)
For Each c In straddress
If c.Value <> 0 Then
strtemp = c.Formula
LResult = StrComp(Left(strtemp, 7), "=ROUND(", vbTextCompare)
If LResult <> 0 Then
'If you want to enter different digits for different regions you have selected,
'activate next line and deactivate previous iNum line.
iNum = Application.InputBox("Decimal", xTitleId, Type:=1)
c.Formula = "=ROUND(" & Right(c.Formula, Len(c.Formula) - 1) & "," & iNum & ")"
End If
End If
Next c
End Sub