We have already explored the IFERROR function in Excel. IFERROR (and IFNA) provide great ways to catch errors in Excel formulas and functions. Now, let’s take it to the next level: Bulk insert IFERROR and IFNA to existing functions. That means, you have many functions already and just want to wrap IFERROR or IFNA around them. Here is how to do that!
Method 1: Insert IFERROR by typing, copy & paste
Just quickly mentioning it here before you start the VBA battle: In some cases, it might be the fastest to examine your Excel calculations and then adjust 2-3 formulas. Next, copy and paste them. This might still be the fastest way.
Method 2: Use a VBA macro to insert IFERROR
How to insert VBA macros
Before we start, just a little reminder of how to insert VBA macros.
- Open the VBA editor. The fastest way to do this would be to press Alt + F11 on the keyboard.
Alternatively, go to the Developer ribbon (if you don’t see the developer ribbon, you must activate it first) and click on Editor.
- Right-click on the Excel file VBA name (or any other item below, for example the “Microsoft Excel Objects” folder icon as in the screenshot).
- Go to Insert and click on “Module”.
- Now it should look similar to number 4 and you see the white code area in which you can paste the VBA source codes from below.
- Press Start to run the macro.
(For more help with VBA macros please refer to this article).
VBA Macro to bulk insert IFERROR functions to existing formulas
Just copy and paste the following lines of code (as described above) into a new VBA module and press start.
Sub insertIFERRORtoCells() On Error Resume Next Dim result As String result = InputBox("What text should be displayed in case of an error?", "Insert IFERROR function: Error Text") If StrPtr(result) <> 0 Then For Each cell In Selection.Cells On Error Resume Next If cell.HasFormula Then cell.Formula = "=IFERROR(" & Right(cell.Formula, Len(cell.Formula) - 1) & "," & result & ")" End If Next End If End Sub
Disadvantages of the VBA method
The VBA macro above solves its purpose. Nevertheless, it comes with some disadvantages:
- All formulas must start with the = sign. Formulas starting with =- or =+ might lead to errors.
- If your existing formula already has an IFERROR function, it won’t be replaced but an additional IFERROR function is added around.
- Of course, it comes with all the other disadvantages of VBA macros (for example, it’s just locally available, difficult to maintain, etc.)
So, please test this VBA macro carefully and better save a backup of your file before you use it!
Method 3: Insert IFERROR with just one click using Professor Excel Tools
Our Excel add-in has more than 120 features to simplify and speed up your work with Excel. One of them is to bulk-insert IFERROR (and IFNA) functions.
Just select all formulas you want to wrap into an IFERROR function, go to IFERROR on the Professor Excel ribbon and type the value or cell reference in case of an error. You can also just leave it blank:
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.