VBA – Excel – Adding “iferror” to many cells with one macro

Posted on December 18, 2013 by

Have you ever had the case to develop a whole solution and at the end someone to tell you the following: “You know, it works well, just insert IFERROR and it is ready to be submitted.”

Then you start rebuilding the whole thing, quite unhappy and wasting about 40 minutes for this process.

Well, officially, this time is OVER! (sounds like a WS-Teleshop commercial)

For this reason, you may use the following macro, simply inserting “IfError” display “” formula to all cells with formula in the selection. Really beautiful! Just select the cells, where you want to apply this (or the whole worksheet) and run the macro.

Sub InsertIFERROR()
    Dim R As Range
    For Each R In Selection.SpecialCells(xlCellTypeFormulas)
        R.Formula = "=IFERROR(" & Mid(R.Formula, 2) & ","""")"
    Next R
End Sub

Sub InsertIFERROR()

Dim R As Range

For Each R In Selection.SpecialCells(xlCellTypeFormulas)

R.Formula = "=IFERROR(" & Mid(R.Formula, 2) & ","""")"

Next R

End Sub

This is really a powerful solution and it works great! Just do not run the macro 2 times, as far as the result would be two IfError Formulas. So run it once per selection and run it carefully! 🙂