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

John, I’m not familiar with Hyperion or your need to round numbers before submitting the data. I wonder if you could get around that need by setting the precision as displayed option? Here is a macro that does that and then unsets the option. The result is that all numbers are set to whatever precision their number format indicates (probably 2 decimal places for accounting data):

Would that achieve the same result? If so, it is probably considerably faster. This was just an idea that occurred to me as I was reading your post, so I haven’t thought through every contingency.

Tim,

Precision as displayed would work great if it could be applied to just one worksheet within a single workbook only.  Unfortunately Excel applies it to the entire workbook and, as a result, when you have multiple complex calculations that build on each other the end result often ends up being materially off of the desired result.

Another reason specific to where I work is that our audit group frowns on linking workbooks.  If we could link a single worksheet to the end result in our budget, precision as displayed would work.

Thanks again.

John