Macro to wrap an existing formula with IFERROR

Today I realized that one of my GETPIVOTDATA formulas was returning an error, since there wasn't any result in the Pivot Table it was checking for data. Unfortunately, during the design of the spreadsheet I never wrapped the function with IFERROR to avoid this.

The challenge I had was that I had several different formulas that needed to be wrapped, and didn't want to spend the time adjusting each one manually. So I wrote a little macro to adjust the existing formulas. Basically what this does it checks each cell in the selection and, if it has a formula in it, wraps it within the following construct: =IFERROR(existing formula, 0) The point? Now if an error is returned, it will return 0 instead.

Public Sub WrapWithIfError()

Dim cl As Range

 

For Each cl In Selection

If cl.HasFormula Then _

cl.Formula = "=IFERROR(" & Right(cl.Formula, Len(cl.Formula) - 1) & ",0)"

Next cl

End Sub

Just a heads up here though… you'll need Excel 2007 or later to make use of the IFERROR function.

7 thoughts on “Macro to wrap an existing formula with IFERROR

  1. Hi Jeff,

    I don't see any reason why not. The only issue you'd have is that it could potentially cut some of the IFERROR functionality when finally moved to Excel 2007/2010 if it wasn't written as robustly. I'd tend to shy away from the UDF though, and just use an IF statement to wrap it instead: =IF(ISNA(GetPivotData),0,GetPivotData)

  2. Hi Ken,

    I use code like this:

    Sub ChangeFormulas()
    Dim oCell As Range
    Dim sFormula As String
    Dim sInput As String
    Dim oDone As Range
    Dim bFirst As Boolean
    Static sFormulaTemplate As String
    If sFormulaTemplate = "" Then
    sFormulaTemplate = "=IF(ISERROR(_form_),"""",_form_)"
    End If
    sInput = InputBox("Enter base formula", , sFormulaTemplate)
    If sInput = "" Then Exit Sub
    sFormulaTemplate = sInput
    For Each oCell In Selection
    If oCell.HasFormula Then
    sFormula = Replace(sFormulaTemplate, "_form_", Right(oCell.Formula, Len(oCell.Formula) - 1))
    If bFirst = False Then
    bFirst = True
    Set oDone = oCell
    If oCell.HasArray Then
    oCell.CurrentArray.FormulaArray = sFormula
    Set oDone = Union(oDone, oCell.CurrentArray)
    Else
    oCell.Formula = sFormula
    Set oDone = Union(oDone, oCell)
    End If
    ElseIf Intersect(oDone, oCell) Is Nothing Then
    If oCell.HasArray Then
    oCell.CurrentArray.FormulaArray = sFormula
    Set oDone = Union(oDone, oCell.CurrentArray)
    Else
    oCell.Formula = sFormula
    Set oDone = Union(oDone, oCell)
    End If
    End If
    End If
    Next
    End Sub

  3. Wow... now that is cool! I had to give that a pretty thorough read...

    So basically, this allows you to update any existing formula to what you specified in the sFormulaTemplate line. Very nice!

  4. Pingback: Daily Dose of Excel » Blog Archive » One of these things is not like the other…

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *