Add ERROR trap: Wrap all your formulas in IFERROR or ISERROR

    Browse: Home / Add ERROR trap: Wrap all your formulas in IFERROR or ISERROR

Wrap Selected Formulas in IFERROR (Excel 2007 and up)

Sub Add_IFERROR_Selection()
Dim myCell As Range
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
        End If
    Next
End Sub

Wrap all formulas in Activesheet in IFERROR (Excel 2007 and up)

Sub Add_IFERROR_Activesheet()
Dim myCell As Range
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            myCell.Formula = "=IFERROR(" & Right(myCell.Formula, Len(myCell.Formula) - 1) & ",0)"
        End If
    Next
End Sub

Wrap Selected Formulas in ISERROR (Excel 2003 and up)

Sub Add_IFISERROR_Selection()
Dim myCell As Range
Dim cFrm As String
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            cFrm = Right(myCell.Formula, Len(myCell.Formula) - 1)
            myCell.Formula = "=IF(ISERROR(" & cFrm & "),0," & cFrm & ")"
        End If
    Next
End Sub

Wrap all formulas in Activesheet in ISERROR (Excel 2003 and up)

Sub Add_IFISERROR_Sheet()
Dim myCell As Range
Dim cFrm As String
    Cells.SpecialCells(xlCellTypeFormulas, 23).Select
    For Each myCell In Selection.Cells
        If myCell.HasFormula And Not myCell.HasArray Then
            cFrm = Right(myCell.Formula, Len(myCell.Formula) - 1)
            myCell.Formula = "=IF(ISERROR(" & cFrm & "),0," & cFrm & ")"
        End If
    Next
End Sub

Source: Excelitems.com

«
»