+ Reply to Thread
Results 1 to 3 of 3

Way to Turn Off Error Checking on a Range?

  1. #1
    James Cox
    Guest

    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. #2
    Vasant Nanavati
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    James Cox
    Guest

    Re: 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
    news:%23J%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1