Macro to wrap an existing formula with IFERROR

Posted on August 27th, 2010 in Excel,Office 2007,Office 2010 by Ken Puls

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.

6 Comments

6 Responses to 'Macro to wrap an existing formula with IFERROR'

Subscribe to comments with RSS or TrackBack to 'Macro to wrap an existing formula with IFERROR'.

  1. jeff weir said,

    on August 27th, 2010 at 8:08 pm

    If you didn’t have excel 2007, couldn’t you just make a custom IFERROR function?

  2. Ken Puls said,

    on August 29th, 2010 at 12:04 pm

    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)

  3. Jan Karel Pieterse said,

    on August 29th, 2010 at 10:39 pm

    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

  4. Ken Puls said,

    on August 30th, 2010 at 6:09 am

    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!

  5. Chris Read said,

    on September 5th, 2010 at 11:10 am

    Hi Ken,

    I you’re ever looking to convert all your IFERROR() functions to IF(ISERROR()) for compatibility with pre-2007, I put together the following tip:

    http://www.professionalexcel.com/index.shtml?tips_01.html

    Regards,

    Chris

  6. Ken Puls said,

    on September 7th, 2010 at 9:42 pm

    Cheers, thanks Chris!

Post a comment