Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Finding errors with VBA (2000sr1a)

    I have columns of calculated values which use the format =If(ISERROR(expression),NA(),expression) and then routines which use filters and SUBTOTAL to derive aggregate functions from the valid data. I recently ran across the problem where on of my users changed a cell to read =G42IF(.... presumably by selecting G42 having accidentally double clicked on the cell in question. I have tried to create an error handler to deal with this, and I'm struggling. I've tried using FilteredRange.Find("#NAME?") and FilteredRange.Find(xlErrName), neither of which work, and when I tried this:

    For r = 1 To FilteredRange.Rows.Count
    If IsError(FilteredRange.Cells(r, 1)) Then
    FilteredRange.Cells(r, 1).Activate
    MsgBox "There is an error in this cell. Please correct it and try again"
    Exit Sub
    End If
    Next r

    then it just bails out as soon as it hits a dodgy cell. If I step through it I get to "Next r" in the cell before and then it just jumps to End sub. I've also tried playing around with CVErr and the like, but the result is the same.

    What am I missing?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding errors with VBA (2000sr1a)

    Maybe like this:

    Sub test()
    Dim ocell As Range
    For Each ocell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, xlErrors)
    MsgBox "The Cell " & ocell.Address & " Contains an error!!!"
    Next
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding errors with VBA (2000sr1a)

    The following might help:

    <pre>Public Sub FixErr()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, xlErrors)
    If oCell.Text <> "#N/A" Then
    oCell.Select
    MsgBox oCell.Address & " Contains an error! Please correct and try again."
    Exit Sub
    End If
    Next
    End Sub
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding errors with VBA (2000sr1a)

    Thanks very much guys, I will try this. How did I not come across this attribute of special cells when poring through the online help for errors?

    One follow up, and a v.stupid question. How do i test if a range contains no cells?

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Finding errors with VBA (2000sr1a)

    The SpecialCells method arguments, including xlErrors, are pretty well documented in XL97, though looking for Errors in Help may not have gotten you to the use in SpecialCells arguments.

    On your second question, I'm guessing you mean "range contains no error cells" rather than "range contains no cells"? SpecialCells is neat, and fast, but errors out if it doesn't find cells matching its criteria. One way around this is to use "on error resume next and then test to see if it found cells.

    Public Sub FixErr()
    Dim rngTarget As Range, rngCell As Range
    On Error Resume Next
    Set rngTarget = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, xlErrors)
    If rngTarget Is Nothing Then
    Exit Sub
    Else
    For Each rngCell In rngTarget
    If rngCell.Text <> "#N/A" Then
    rngCell.Select
    MsgBox rngCell.Address & " Contains an error! Please correct and try again."
    Exit Sub
    End If
    Next
    End If
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Finding errors with VBA (2000sr1a)

    No, it was way more obvious than that. I didn't know how to text "Nothingness", and again online help didn't.

    Is nothing.

    Thanks

Posting Permissions

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