Wrap ROUND and ISERROR Around Formulas

In our accounting shop, we build our budgets in Excel but submit them in another software called Hyperion. Hyperion requires us to submit in whole numbers. To do so, we need to round every formula in the final Hyperion load so that the budget foots and crossfoots.

Before submission, I'll use the following macro to wrap the ROUND function around every formula:

Sub Add_Rounding()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Round")) = 0 Then
            Rng.Formula = "=round(" & cellFormula & ",0)"
        End If
    
    Next Rng

End Sub

The macro works by:

• Looping through each cell in the range.
• Uses the Special Cells method to identify only those cells with formulas.
• Sets up a string called "cellFormula" which is a picture of the formula.
• Uses the InStr function to look at the cellFormula string and identify if it already has the ROUND function included. If so, the macro goes on to the next cell. If not, it adds the ROUND function to the formula.

You can use the same type of logic to apply the ISERROR function to a range of formulas:

Sub Add_ErrorTrap()

    Dim cellRange As Range
    Dim Rng As Range
    Dim cellFormula As String

    On Error Resume Next
    
    Set cellRange = Range("A1:C10").SpecialCells(xlCellTypeFormulas)

    For Each Rng In cellRange
        
        cellFormula = Mid(Rng.Formula, 2, 1024)
        If InStr(UCase(cellFormula), UCase("Error")) = 0 Then
            Rng.Formula = "=if(Iserror(" & cellFormula & "),0," & cellFormula & ")"
        End If
    
    Next Rng

End Sub