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”
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…