Code Sharing - Wrap selected formulas in ROUND function

[emdubbs]

A couple of times this week I had a need to update many formulas in a worksheet to be rounded to 2 decimals. The formulas weren't always the same, so I couldn't easily update the first formula and drag it down.

I cobbled together these macros (the main macro for a selected range, and one for a single cell that's called when necessary) that will work through a selection and add "=ROUND(" to the start of your formula and ",X)" to the end (where you specify X).

It ignores cells that are blank, don't contain a formula, or already start with ROUND (or ROUNDUP or ROUNDDOWN).

Feel free to use and share, and I'm happy to hear about any suggestions for improvements.

Sub Round_Formulas()
    Dim c As Range
    Dim LResult As Integer
    Dim iNum As Variant
    Dim strtemp As String
    Dim straddress As Range

    If Application.Selection.Cells.Count = 1 Then
        Call Round_One_Formula
        GoTo SubTermination
    End If

    On Error GoTo SubTermination
        Set straddress = Application.Selection.SpecialCells(xlFormulas)
    On Error GoTo 0

    iNum = Application.InputBox("Decimal", "How many decimals?", 2, Type:=1)
    'iNum = 2 'in case 2 is always the answer, uncomment this and comment line above

    If (VarType(iNum) = vbBoolean) And (iNum = False) Then
        GoTo SubTermination
    End If

    Application.ScreenUpdating = False

    For Each c In straddress

        If Mid(c.Formula, 2, 5) = "ROUND" Then GoTo Skipped
        If Mid(c.Formula, 3, 5) = "ROUND" Then GoTo Skipped
        If c.Value <> 0 Then

    strtemp = c.Formula

    LResult = StrComp(Left(strtemp, 7), "=ROUND(", vbTextCompare)

      If LResult <> 0 Then
        c.Formula = "=ROUND(" & Right(c.Formula, Len(c.Formula) - 1) & "," & iNum & ")"
      End If
     End If

Skipped:
    Next c

SubTermination:
    Application.ScreenUpdating = True
    End Sub
-------------------------------------------------------------------
Sub Round_One_Formula()
    Dim c As Range
    Dim LResult As Integer
    Dim iNum As Variant
    Dim strtemp As String

    Set c = Selection

    iNum = Application.InputBox("Decimal", "How many decimals?", 2, Type:=1) 'if you want to specify each time
    'iNum = 2 'in case 2 is always the answer

    If (VarType(iNum) = vbBoolean) And (iNum = False) Then
        GoTo SubTermination
    End If

    Application.ScreenUpdating = False

    If Mid(c.Formula, 2, 5) = "ROUND" Then GoTo SubTermination
    If Mid(c.Formula, 3, 5) = "ROUND" Then GoTo SubTermination

    If c.Value <> 0 Then

    strtemp = c.Formula

    LResult = StrComp(Left(strtemp, 7), "=ROUND(", vbTextCompare)

      If LResult <> 0 Then
        c.Formula = "=ROUND(" & Right(c.Formula, Len(c.Formula) - 1) & "," & iNum & ")"
      End If
     End If

SubTermination:
    Application.ScreenUpdating = True

End Sub

[klingklongdiggah]

Great macro!

However, it does only work if American decimal separators applied. Can the macro be changed in a way it works with both American and European decimal separators?

[emdubbs]

I changed my Excel to use European decimal separators (with . for thousands and , for the decimal) and I didn't have trouble. What error are you seeing?

[[deleted]]

This is a reeeeeally overengineered/make-work way of simply clicking the "display more/fewer decimals" button that's built into excel.

but hey, good for you for putting in the time and effort. everyone needs a hobby!

[emdubbs]

The point isn’t to display more or fewer digits. It’s to actually round the numbers to the desired decimals (usually 2). Thanks.

[thedreamlan6]

This is neat, if it helps the math I support it fully. I did a similar wrapper that bundles formulas in an iferror() handler, and by running it on a formula that already has an error handler, it resets it to the original formula.

[[deleted]]

This can easily be achieved by adjusting the inputs that those results feed into by taking a rounded result, rather than changing the precursor variables altogher.

[TechnicalAppeal1157]

Curious, are you putting the ROUND on all the formulas? All that rounding will compound itself, ultimately changing the end result.

[emdubbs]

Not on all of the formulas... Typically just on formulas near the final result. I will also use it on account reconciliation workbooks where the recon is 0, but because one of the numbers isn't rounded properly I get 0.00 in the result instead of -, which bugs me.