Wrap excel formulas in error trapping code

All the random crap I spend all day long figuring out.
Post Reply
User avatar
syntax
Site Admin
Posts: 54
Joined: Tue Jan 06, 2009 9:25 pm

Wrap excel formulas in error trapping code

Post by syntax » Tue Dec 18, 2012 3:25 pm

http://www.excelitems.com/2011/03/wrap- ... s-add.html

Excel 2007

Code: Select all

Wrap Selected Formulas
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
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
Excel 2003

Code: Select all

Wrap Selected Formulas
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
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
Caution:
1. Change the red highlighted 0 with the replacement you would like to have instead of 0.
2. These macros do not check if existing formulas are wrapped with iferror or iserror.
3. Make your selection wisely, you may not need to wrap all formulas with Error trap.

Post Reply