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)"
Just a heads up here thoughâ€¦ you'll need Excel 2007 or later to make use of the IFERROR function.