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.

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

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)

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

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!

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

Cheers, thanks Chris!

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