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