Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Way to Turn Off Error Checking on a Range?
Is there a way to turn off error checking on a range? It can be done by
clicking on the little error symbol and selecting the option to ignore the error ("Yes, I know that empty cells were in the sum formula but it's OK!!!!"), but when I try to record this action as a keystroke macro, I get nothing :( Any wisdom out there on this? James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Way to Turn Off Error Checking on a Range?
Range("A1").Errors(xlEmptyCellReferences).Ignore = True
Unfortunately, it doesn't seem to work on multi-cell ranges, at least for me, so you may have to loop through every cell in the range. -- Vasant "James Cox" wrote in message ... Is there a way to turn off error checking on a range? It can be done by clicking on the little error symbol and selecting the option to ignore the error ("Yes, I know that empty cells were in the sum formula but it's OK!!!!"), but when I try to record this action as a keystroke macro, I get nothing :( Any wisdom out there on this? James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Way to Turn Off Error Checking on a Range?
Thanks, Vasant -
I got trapped in a meeting where my attendance but not participation was required (yes, I DO work with Dilbert) and scratched out the following code that works pretty well - just highlight the range of cells and run the applicable macro (as usual, be wary of the the effects of line-wrap in this message!) : Public Sub cancelErrorCheck() 'Possible values of xlErrorChecks 'xlEvaluateToError = 1 'xlTextDate = 2 'xlNumberAsText = 3 'xlInconsistantConstant = 4 'xlOmittedCells = 5 'xlUnlockedFormulaCells = 6 'xlEmptyCellReferences = 7 Dim origCell As Object Dim iI As Integer 'Take 'em all out For Each origCell In Selection For iI = 1 To 7 origCell.Errors(iI).Ignore = True Next iI Next origCell End Sub Public Sub restoreErrorCheck() Dim origCell As Object Dim iI As Integer 'Put 'em all back in For Each origCell In Selection For iI = 1 To 7 origCell.Errors(iI).Ignore = False Next iI Next origCell End Sub Public Sub checkErrorCheck() Dim origCell As Object Dim iI As Integer Dim strErrorChecks As String Dim bErrCk1 As Boolean Dim bErrCk2 As Boolean Dim bErrCk3 As Boolean Dim bErrCk4 As Boolean Dim bErrCk5 As Boolean Dim bErrCk6 As Boolean Dim bErrCk7 As Boolean strErrorChecks = "" bErrCk1 = False bErrCk2 = False bErrCk3 = False bErrCk4 = False bErrCk5 = False bErrCk6 = False bErrCk7 = False For Each origCell In Selection For iI = 1 To 7 If origCell.Errors(iI).Ignore = True Then Select Case iI Case 1 If bErrCk1 = False Then bErrCk1 = True strErrorChecks = strErrorChecks & " xlEvaluateToError" & vbLf End If Case 2 If bErrCk2 = False Then bErrCk2 = True strErrorChecks = strErrorChecks & " xlTextDate" & vbLf End If Case 3 If bErrCk3 = False Then bErrCk3 = True strErrorChecks = strErrorChecks & " xlNumberAsText" & vbLf End If Case 4 If bErrCk4 = False Then bErrCk4 = True strErrorChecks = strErrorChecks & " xlInconsistantConstant" & vbLf End If Case 5 If bErrCk5 = False Then bErrCk5 = True strErrorChecks = strErrorChecks & " xlOmittedCells" & vbLf End If Case 6 If bErrCk6 = False Then bErrCk6 = True strErrorChecks = strErrorChecks & " xlUnlockedFormulaCells" & vbLf End If Case 7 If bErrCk7 = False Then bErrCk7 = True strErrorChecks = strErrorChecks & " xlEmptyCellReferences" & vbLf End If Case Else strErrorChecks = strErrorChecks & " Some strange code = " & CStr(iI) & " was detected." End Select End If Next iI Next origCell If strErrorChecks = "" Then MsgBox "No cells in the selected range have any of the Error Checks turned off." Else MsgBox "One or more of the cells in the selected range" & vbLf & "have the following Error Checks turned off:" & vbLf & vbLf & strErrorChecks End If End Sub Thanks for the starter concept! James "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Range("A1").Errors(xlEmptyCellReferences).Ignore = True Unfortunately, it doesn't seem to work on multi-cell ranges, at least for me, so you may have to loop through every cell in the range. -- Vasant "James Cox" wrote in message ... Is there a way to turn off error checking on a range? It can be done by clicking on the little error symbol and selecting the option to ignore the error ("Yes, I know that empty cells were in the sum formula but it's OK!!!!"), but when I try to record this action as a keystroke macro, I get nothing :( Any wisdom out there on this? James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error checking | Excel Discussion (Misc queries) | |||
Error Checking | Excel Discussion (Misc queries) | |||
Error Checking | Excel Discussion (Misc queries) | |||
Global turn-off of Error Checking - little yellow squares | Excel Discussion (Misc queries) | |||
Provide a way to turn off auto-checking excel formulas as I type t | Excel Discussion (Misc queries) |