Excel – Ignore formula errors with VBA code

To totally unlock this section you need to Log-in

Login
To remove the annoying error flags (with a green triangle in the corner and an exclamation point with a drop-down when you enter the cell) in Excel spreadsheets that have a different formula for adjacent cells there is the following code:

Sub IgnoreErrors(Optional wsheet As Worksheet)


Dim cell As Range


Dim intLoop As Integer


Dim strEndCell as String


    On Error Resume Next


    If wsheet Is Nothing Then Set wsheet = ActiveSheet


    strEndCell = wsheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address


    For Each cell In wsheet.Range("$A$1:" & strEndCell)


        For intLoop = 1 To 8


            cell.Errors.Item(intLoop).Ignore = True


         Next


     Next    


End Sub

How to determine the actual number of errors in the cell

There are 7 type of errors:

1 xlEvaluateToError


2 xlTextDate


3 xlNumberAsText


4 xlInconsistentFormula


5 xlOmittedCells


6 xlUnlockedFormulaCells


7 xlEmptyCellReferences


See as reference: Microsoft Library - 236967.

Counting errors

To count how many errors there are in a cell we could use a macro as the following:

Sub CountErrors()


    Dim cell As Range, nErrors As Integer


    Set cell = [A1]


    For i = 1 To 7


        If cell.Errors.Item(i).Value Then nErrors = nErrors + 1


    Next i


    MsgBox "Cell A1 has " & nErrors & " errors."


End Sub

You've just the article: Excel - Ignore formula errors with VBA code

Search by Keyword