Ignore Excel formula errors using VBA

[CZahrobsky]

I've written a macro 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:

Sub subIgnoreErrors(Optional ws As Worksheet)
Dim cell As Range
Dim intLoop As Integer
Dim strEndCell as String
    On Error Resume Next
    If ws Is Nothing Then Set ws = ActiveSheet
    strEndCell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Address
    For Each cell In ws.Range("$A$1:" & strEndCell)
        For intLoop = 1 To 8
            cell.Errors.Item(intLoop).Ignore = True
         Next
     Next    
End Sub

My question is, is there a way to determine the actual number of errors in the cell? Eight seems to be the magic number

[SammyB]

There are 7:
1 xlEvaluateToErr or
2 xlTextDate
3 xlNumberAsText
4 xlInconsistentF ormula
5 xlOmittedCells
6 xlUnlockedFormu laCells
7 xlEmptyCellRefe rences
See http://msdn2.microsoft.com/en-us/library/Bb236967.aspx

So, I wrote a test macro that counts the number of errors in A1:

Sub Macro1()
	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

[dtecmeister]

I don't know the version this came in, but you can now use:
ErrorCheckingOp tions.NumberAsT ext = False
Others available as listed below.
Thanks for the info though!

There are 7:
1 xlEvaluateToErr or
2 xlTextDate
3 xlNumberAsText
4 xlInconsistentF ormula
5 xlOmittedCells
6 xlUnlockedFormu laCells
7 xlEmptyCellRefe rences
See http://msdn2.microsoft.com/en-us/library/Bb236967.aspx

So, I wrote a test macro that counts the number of errors in A1:

Sub Macro1()
	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