Need to remove Round Formula from a Formula which has a Round Function

[savioxfernandes]

HI

i currently have the beow code which can add an round formula to an Exiting formula in a cell

Sub add_round()
Dim rng As Range
    For Each rng In Selection
        rng.Value = "=Round(" & Mid(rng.Formula, 2, 9999) & ",2)"
       Next rng


End Sub

however i need to undo this -

meaninig
if the result of the above code give me "=round ((Sum(A1+A2),2) =24.44
then i need to remove the round form the formula in an range

thanks
Savio Fernandes

 

[RoryA]

You could use something like this:

Sub remove_round()
    Dim rng As Range, formulasRange As Range
    Dim theFormula As String
    
    On Error Resume Next
    If Selection.Cells.Count > 1 Then
        Set formulasRange = Selection.SpecialCells(xlCellTypeFormulas)
    Else
        Set formulasRange = Selection
    End If
    On Error GoTo 0
    
    If Not formulasRange Is Nothing Then
        Application.ScreenUpdating = False
        For Each rng In formulasRange.Cells
            theFormula = rng.Formula
            If UCase$(Left$(theFormula, 7)) = "=ROUND(" Then
                theFormula = "=" & Mid(rng.Formula, 8)
                theFormula = Left$(theFormula, InStrRev(theFormula, ",") - 1)
                rng.Formula = theFormula
            End If
        Next rng
    End If
End Sub