This is a fantastic little macro that I picked up from The Spreadsheet Guru
I seem to use the GETPIVOTDATA function on a near daily basis to generate fixed format reports, it is such a flexible tool for creating dynamic reports that are formatted just as you require – without the limitations of the original pivot table, but just occasionally I have a problem with it.
So for example I was trying to whizz through some planning templates for 5 CCG’s and I was copy and pasting GETPIVOTDATA formulas across each, only to find the dreaded #REF error appearing all over the place because I was referencing a field name that didn’t exist in one of the CCG’s source pivot tables.
It’s easy to fix by wrapping the GETPIVOTDATA formula in its own error detection wrapper – the IFERROR function, but I had a lot of formulas to wrap which made the prospect rather daunting.
This macro was a life saver.
Copy it to a module in your personal macro workbook and you can use it across any workbook you have open. To use it, just select the cell or range with the formulas you want to adjust and then run the macro.
Sub WrapIfError() 'PURPOSE: Add an IFERROR() Function around all the selected cells' formulas 'SOURCE: www.TheSpreadsheetGuru.com Dim rng As Range Dim cell As Range Dim x As String 'Determine if a single cell or range is selected If Selection.Cells.Count = 1 Then Set rng = Selection If Not rng.HasFormula Then GoTo NoFormulas Else 'Get Range of Cells that Only Contain Formulas On Error GoTo NoFormulas Set rng = Selection.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 End If 'Loop Through Each Cell in Range and add =IFERROR([formula],"") For Each cell In rng.Cells x = cell.Formula cell = "=IFERROR(" & Right(x, Len(x) - 1) & "," & Chr(34) & Chr(34) & ")" Next cell Exit Sub 'Error Handler NoFormulas: MsgBox "There were no formulas found in your selection!" End Sub