# Wrapping an IFERROR Function Around Your Formulas

/**What This VBA Code Does**

This VBA macro will add an IFERROR function around any cell formula in a given selection. I have coded the IFERROR function to return a blank value if the the formula calculates an error. You can certainly change the macro to make it return a 0 or any other value you wish. This can be used to make your spreadsheets look nicer or to allow sum functions to calculate properly if there is an error in one of the inputs.

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

I took the above macro a step further and gave it some flexibility. The following VBA code will cycle through wrapping the IFERROR function around formulas and either making the True output be a zero or a blank value. Just keep running the macro until you get your desired outcome!

Sub WrapIfError_v2()

'PURPOSE: Add an IFERROR() Function around all the selected cells' formulas. _

Also handles if IFERROR is already wrapped around formula.

'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range

Dim cell As Range

Dim AlreadyIFERROR As Boolean

Dim RemoveIFERROR As Boolean

Dim TestEnd1 As String

Dim TestEnd2 As String

Dim TestStart As String

Dim MyFormula As String

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

'Get formula from First cell in Selected Range

MyFormula = rng(1, 1).Formula

'Create Test Strings To Determine if IFERROR formula has already been added

TestEnd1 = Chr(34) & Chr(34) & ")"

TestEnd2 = ",0)"

TestStart = Left(MyFormula, 9)

'Determine How we want to modify formula

If Right(MyFormula, 3) = TestEnd1 And TestStart = "=IFERROR(" Then

Beg_String = ""

End_String = "0)" '=IFERROR([formula],0)

AlreadyIFERROR = True

ElseIf Right(MyFormula, 3) = ",0)" And TestStart = "=IFERROR(" Then

RemoveIFERROR = True

Else

Beg_String = "=IFERROR("

End_String = "," & Chr(34) & Chr(34) & ")" '=IFERROR([formula],"")

AlreadyIFERROR = False

End If

'Loop Through Each Cell in Range and modify formula

For Each cell In rng.Cells

x = cell.Formula

If RemoveIFERROR = True Then

cell = "=" & Mid(x, 10, Len(x) - 12)

ElseIf AlreadyIFERROR = False Then

cell = Beg_String & Right(x, Len(x) - 1) & End_String

Else

cell = Left(x, Len(x) - 3) & End_String

End If

Next cell

Exit Sub

'Error Handler

NoFormulas:

MsgBox "There were no formulas found in your selection!"

End Sub

**How Do I Modify This To Fit My Specific Needs?**

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: **My Guide to Getting the Solution to your Problems FAST!** In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, *you name it*!

I highly recommend that you check **this guide** out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)