IF-ISERROR formula (VBA shortcut)

[KWMSeattle]

I work in finance and often use formulas to calculate variances between two figures. Many of my spreadsheets are quite large at this point and there are occasional zeroes in the data. This creates #DIV/0! errors in my variance formulas.

For example, let's say I have the following formula to calculate year-over-year variance for two numbers:

EXAMPLE 1 =B1/A1-1

This works fine if the value in A1 is not zero. So when I see a #DIV/0! error I go into the formula and change it to:

EXAMPLE 2 =IF(ISERROR(B1/A1-1),"n/a",B1/A1-1)

This will show n/a if the formula is equating to an error.

Since I have so many workbooks with variance calculations and the IF-ISERROR formula is not easy to type out quickly, is there a way to use VBA to modify the formula in the ActiveCell to bring in the IF-ISERROR syntax? Maybe something like this:

Take original formula from Example 1 above, add "IF(ISERROR(" before it, then add the n/a part, and finally put the origianl formula at the end and enclose in a closing parenthesis.

Thanks in advance for any tips!

 

[barry houdini]

Don't know about VBA but you don't need ISERROR to suppress #DIV/0!. Try

=IF(A1,B1/A1-1,"n/a")

 

[KWMSeattle]

SOLVED!

With some fiddling around I was able to resolve my question. Here is the code I am using:

Sub IF_ISERROR()

Dim OrigFormula As String
Dim NewFormula As String

    If ActiveCell.Formula = "" Then
    MsgBox "The active cell does not contain a formula", vbOKOnly, "No Formula"
    Exit Sub
    End If

OrigFormula = ActiveCell.Formula
NewFormula = Mid(OrigFormula, 2, Len(OrigFormula) - 1)

ActiveCell.Formula = "=IF(ISERROR(" & NewFormula & ")," & """n/a""" & "," & NewFormula & ")"

End Sub