I have a sheet with numerous ROUND functions. I want them all removed. How
can I do a search/replace to remove them all at once.

For example, I want to replace
=ROUND(A1*B1,1)
with this...
=A1*B1

They all end with ,1)

Thanks in advance.

This is a general purpose Round removing routine. It operates on the
selected range.

Sub DoReplaceRounds()
Dim Cell As Range, RoundStart As Integer
Dim Strg As String, Ptr As Integer
Dim FoundOne As Boolean, Char As String
Dim Level As Integer, CommaPtr As Integer
Dim SrchRg As Range
Dim RemoveParen As Integer
On Error GoTo EndThis
RemoveParen = 1 '0 to keep parens
If Selection.Cells.Count = 1 Then
Set SrchRg = ActiveCell
Else
Set SrchRg = Selection.SpecialCells(xlCellTypeFormulas)
End If
For Each Cell In SrchRg
FoundOne = False
Strg = Cell.Formula
FindNext:
RoundStart = InStr(1, Strg, "round(", vbTextCompare)
If RoundStart 0 Then
Level = 1
For Ptr = RoundStart + 6 To Len(Strg)
Char = Mid(Strg, Ptr, 1)
If Char = "(" Then
Level = Level + 1
ElseIf Char = ")" Then
Level = Level - 1
ElseIf Char = "," Then
If Level = 1 Then CommaPtr = Ptr
End If
If Level = 0 Then Exit For
Next

Strg = Application.Replace(Strg, CommaPtr, Ptr - CommaPtr +
RemoveParen, "")

Strg = Application.Replace(Strg, RoundStart, 5 + RemoveParen,
"")
FoundOne = True
GoTo FindNext ''May have more than 1 Round in the formula
Else
If FoundOne Then Cell.Formula = Strg
End If
Next
EndThis:
End Sub

--
Jim
"Arun" wrote in message
...
I have a sheet with numerous ROUND functions. I want them all removed.
How
can I do a search/replace to remove them all at once.

For example, I want to replace
=ROUND(A1*B1,1)
with this...
=A1*B1

They all end with ,1)

Thanks in advance.

Awesome!! Thanks.

"Jim Rech" wrote:

This is a general purpose Round removing routine. It operates on the
selected range.

Sub DoReplaceRounds()
Dim Cell As Range, RoundStart As Integer
Dim Strg As String, Ptr As Integer
Dim FoundOne As Boolean, Char As String
Dim Level As Integer, CommaPtr As Integer
Dim SrchRg As Range
Dim RemoveParen As Integer
On Error GoTo EndThis
RemoveParen = 1 '0 to keep parens
If Selection.Cells.Count = 1 Then
Set SrchRg = ActiveCell
Else
Set SrchRg = Selection.SpecialCells(xlCellTypeFormulas)
End If
For Each Cell In SrchRg
FoundOne = False
Strg = Cell.Formula
FindNext:
RoundStart = InStr(1, Strg, "round(", vbTextCompare)
If RoundStart 0 Then
Level = 1
For Ptr = RoundStart + 6 To Len(Strg)
Char = Mid(Strg, Ptr, 1)
If Char = "(" Then
Level = Level + 1
ElseIf Char = ")" Then
Level = Level - 1
ElseIf Char = "," Then
If Level = 1 Then CommaPtr = Ptr
End If
If Level = 0 Then Exit For
Next

Strg = Application.Replace(Strg, CommaPtr, Ptr - CommaPtr +
RemoveParen, "")

Strg = Application.Replace(Strg, RoundStart, 5 + RemoveParen,
"")
FoundOne = True
GoTo FindNext ''May have more than 1 Round in the formula
Else
If FoundOne Then Cell.Formula = Strg
End If
Next
EndThis:
End Sub

--
Jim
"Arun" wrote in message
...
I have a sheet with numerous ROUND functions. I want them all removed.
How
can I do a search/replace to remove them all at once.

For example, I want to replace
=ROUND(A1*B1,1)
with this...
=A1*B1

They all end with ,1)

Thanks in advance.