Quickly Wrap Formulas in IFERROR without VBA

Here’s a quick tip for quickly wrapping all formulas in IFERROR without VBA.

 

1. Select the cells that contain the formulas you want wrapped in IFERROR.

 

2. Press F5 on your keyboard to activate the GoTo dialog box, then click Special.

 

3. Select Formulas and press the OK button

 

4. In the Formula bar, enter the IFERROR function you’d like to use, then press CTRL+ENTER. Pressing Ctrl+Enter is important here. Make sure you don’t press just the Enter key.


 

5. Marvel at your efficiency.

 

Of course, this handy trick only works if all your formulas are performing similar calculations (referencing the same relative cells). If want to wrap formulas that are performing different operations, you’ll need to use some VBA. Here’s a quick and dirty macro that wraps selected formulas in IFERROR.

  1. Sub Add_IFERROR()
  2. Dim R As Range
  3.  
  4. For Each R In Selection.SpecialCells(xlCellTypeFormulas)
  5.  
  6.      If Left(R.Formula, 8) <> "=IFERROR" Then
  7.           R.Formula = "=IFERROR(" & Mid(R.Formula, 2) & ","""")"
  8.      End If
  9. Next R
  10.  
  11. End Sub

8 thoughts on “Quickly Wrap Formulas in IFERROR without VBA

  1. MF

    Maybe not a good practice… but as a lazy guy, what I would normally do is to select the range, type the formula in the first cell, and then Ctrl+Enter. 😛

    For inconsistent formula, your code works like a charm! I will keep it in my personal macro for future usage.

    Thanks!

  2. Claude Van Horn

    In EXCEL 2013, I get an error when I try to use the routine. It looks like the =IFERROR string is being interpreted as a valid function (it reformats to ” = IfError” and the line R.Formula = “=IFERROR(” & Mid(R.Formula, 2) & “,””””)” pops up a “Compile Error: Expected end of statement” with the final ” highlighted.

    I fiddled around a bit and manually entered (instead of pasting in) the If statement first line so it does say “=IFERROR” though it took a couple of three tries to get the change accepted. However I still get the error on the R.Formula line.

    Any hints?

  3. datapig Post author

    Calude: It’s probably the quotes copied from WordPress. Try retyping the quotes after pasting the macro into Excel.

    I’ve put the macro in a code box, so you should be able to copy and paste that without having to fix the quotes.

  4. Heather

    I’ll have to try the VBA – hadn’t considered that before.

    My lazy method (from EARLY in my Excel days) is to select the range I need to include IFERROR in, and do a find/replace on the = character. My replacement is A=

    This makes all those formulas text values. Then I use find/replace on ), replacing it with ),””)
    Finally, I go back and replace A= with =, this works a charm – unless I’ve forgotten that I have a more complex formula that is nested. 🙂

  5. Jomili

    I must be lazier than the average bear. I have a similar macro I made a few years ago that works well for me. It allows me to put the replacement value of my choice, instead of just nothing.

    Sub InsertIFERROR()
    ‘in Formula and Analysis Functions
    Dim iReply
    Dim R As Range
    iReply = Application.InputBox(Prompt:=”For TEXT replacement, use quotation marks.” & vbCrLf _
    & vbCrLf & “For NUMERICAL or FORMULA replacement, don’t use quotation marks.”, _
    Title:=”What’s Your Replacement Value?”, Type:=1 Or 2)

    For Each R In Selection.SpecialCells(xlCellTypeFormulas)
    R.Formula = “=IFERROR(” & Mid(R.Formula, 2) & “, ” & iReply & “)”
    Next R

    End Sub

  6. Ben Sacherich

    Great idea. I’ll be adding this to my personal macro. +1 to Jomili for the idea of prompting.

Leave a Reply

Your email address will not be published. Required fields are marked *