If your formulae are working w/decimals you may want to double check to make
sure the rounding is not necessary:
http://cpearson.com/excel/rounding.htm
Personally, if it wasn't hurting anything, I'd probably leave the Round
functions alone.
I think editing the formula is a little tricky. You have to remove the
"Round(" and the second argument (from the "," to the ending ")". And if
Round is nested inside of another function, once the "Round(" is removed, the
")" of the round function might be mistaken for the ")" of the function in
which it was nested. So you have to look for the first comma after "Round("
that is not inside of a pair of "( )" (since the first argument of Round
could be a function call) and then from there find the ")" that does not pair
with a beginning "(" (since the second argument of Round could be another
function or calculation). And, of course, there could be multiple round
functions in one formula.
It seems to work (but I may not have thought of everything), so perhaps try
the following macro (Macro name is Test - which uses the EditFormula
function). I'm sure it could be streamlined and/or made more elegant (or
perhaps there is another approach entirely that would be better and I'm just
making it more complicated than it really is), but it's pretty late, so.....
** Please be sure you maintain a complete backup of your workbook (several
if it is important) **
If there are formulae that you don't want the round function removed from, I
would remove the "=" from the formula (which changes it to text and shouldn't
be included in the SpecialCells(xlCellTypeFormulas) line.
Function EditFormula(strFormula As String, _
strSearch As String, bMultParameters As Boolean)
Dim lngStart As Long
Dim lngCount As Long
Dim lngComma As Long
Dim strSubString As String
Dim i As Long
lngStart = InStr(1, strFormula, strSearch, vbTextCompare)
If lngStart > 0 Then
strSubString = Left(strFormula, Len(strSearch) + _
lngStart - 1)
strSubString = strSubString & EditFormula(Right(strFormula, _
Len(strFormula) - lngStart - Len(strSearch) + 1), _
strSearch, bMultParameters)
If Len(strSubString) > 0 Then
strSubString = Replace(strSubString, strSearch, _
"", 1, 1, vbTextCompare)
For i = lngStart To Len(strSubString)
If Mid(strSubString, i, 1) = "(" Then
lngCount = lngCount + 1
ElseIf Mid(strSubString, i, 1) = "," And _
lngCount = 0 And bMultParameters Then
If lngComma = 0 Then lngComma = i
ElseIf Mid(strSubString, i, 1) = ")" Then
If lngCount = 0 Then
If bMultParameters And lngComma > 0 Then
strSubString = Left(strSubString, lngComma - 1) & _
Right(strSubString, Len(strSubString) - i)
ElseIf Not bMultParameters Then
strSubString = Left(strSubString, i - 1) & _
Right(strSubString, Len(strSubString) - i)
End If
Exit For
Else: lngCount = lngCount - 1
End If
End If
Next i
End If
Else
EditFormula = strFormula
Exit Function
End If
EditFormula = strSubString
End Function
Sub test()
Dim wksTemp As Worksheet
Dim rngTemp As Range
Dim rngCell As Range
For Each wksTemp In Worksheets
On Error Resume Next
Set rngTemp = wksTemp.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo ErrorHandler
If Not rngTemp Is Nothing Then
For Each rngCell In rngTemp
rngCell.Formula = EditFormula(rngCell.Formula, "Round(", True)
Next rngCell
End If
Next wksTemp
ErrorHandler:
If Err.Number <> 0 Then
If Not rngCell Is Nothing Then _
MsgBox "Error: " & rngCell.Parent.Name _
& ", " & rngCell.Address
If MsgBox("Continue", vbYesNo) = vbYes Then _
Resume Next
End If
End Sub
