I have a model that someone made with TONS of round functions, which I have
no use for, since they muddy the formulas, which are already long, and I can
get the effect by just formatting properly.

How can I delete the function, while preserving what's inside? Each
occurence has different characters inside, so I don't know how to
find/replace, since my solution would be to find "round(" and replace with
space, then move to last parentheses, but this will not only give an error
once it tries to finish the first find/replace (it would have an ending paren
with no beginning), but also potentially take out the ending parentheses for
other expressions. Any way to get around this? There are hundreds of
occurences, so any avoidance of manual change would be great.
--
Boris

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


"BorisS" wrote:

I have a model that someone made with TONS of round functions, which I have
no use for, since they muddy the formulas, which are already long, and I can
get the effect by just formatting properly.

How can I delete the function, while preserving what's inside? Each
occurence has different characters inside, so I don't know how to
find/replace, since my solution would be to find "round(" and replace with
space, then move to last parentheses, but this will not only give an error
once it tries to finish the first find/replace (it would have an ending paren
with no beginning), but also potentially take out the ending parentheses for
other expressions. Any way to get around this? There are hundreds of
occurences, so any avoidance of manual change would be great.
--
Boris

Hi. BorisS,

Trying to remove TONS of round function, on a model made by "someone" - with your question about preserving what's inside will surely be answered by a "NO".

The word "what's inside" is not about the single formula but it concerns the desired / integrated results.

If that formulation is modeled for engineering purpose, the model may not suit you at all. If you mean that since they muddy the formulas - doesn't mean they also muddy the desired model result.

If you try to change the formula, it will become your new own model. Try to be cautious.

Happy holidays hohoho...
driller:)