By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,186 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

Ignore Excel formula errors using VBA

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. Sub subIgnoreErrors(Optional ws As Worksheet)
  2. Dim cell As Range
  3. Dim intLoop As Integer
  4. Dim strEndCell as String
  5.     On Error Resume Next
  6.     If ws Is Nothing Then Set ws = ActiveSheet
  7.     strEndCell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Address
  8.     For Each cell In ws.Range("$A$1:" & strEndCell)
  9.         For intLoop = 1 To 8
  10.             cell.Errors.Item(intLoop).Ignore = True
  11.          Next
  12.      Next    
  13. 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
Sep 20 '07 #1
Share this Question
Share on Google+
2 Replies


SammyB
Expert 100+
P: 807
There are 7:
1 xlEvaluateToError
2 xlTextDate
3 xlNumberAsText
4 xlInconsistentFormula
5 xlOmittedCells
6 xlUnlockedFormulaCells
7 xlEmptyCellReferences
See http://msdn2.microsoft.com/en-us/library/Bb236967.aspx

So, I wrote a test macro that counts the number of errors in A1:
Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.     Dim cell As Range, nErrors As Integer
  3.     Set cell = [A1]
  4.     For i = 1 To 7
  5.         If cell.Errors.Item(i).Value Then nErrors = nErrors + 1
  6.     Next i
  7.     MsgBox "Cell A1 has " & nErrors & " errors."
  8. End Sub
  9.  
Sep 21 '07 #2

P: 1
I don't know the version this came in, but you can now use:
ErrorCheckingOptions.NumberAsText = False
Others available as listed below.
Thanks for the info though!

There are 7:
1 xlEvaluateToError
2 xlTextDate
3 xlNumberAsText
4 xlInconsistentFormula
5 xlOmittedCells
6 xlUnlockedFormulaCells
7 xlEmptyCellReferences
See http://msdn2.microsoft.com/en-us/library/Bb236967.aspx

So, I wrote a test macro that counts the number of errors in A1:
Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.     Dim cell As Range, nErrors As Integer
  3.     Set cell = [A1]
  4.     For i = 1 To 7
  5.         If cell.Errors.Item(i).Value Then nErrors = nErrors + 1
  6.     Next i
  7.     MsgBox "Cell A1 has " & nErrors & " errors."
  8. End Sub
  9.  
Feb 28 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.