Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ErrorHandler (XP)

    I have a challenge with code using an ErrorHandler. In the example code only the first encounter of an error is sucessfully processed in the "ErrorHandler". Subsequent errors are not processed and the code hangs at "oTest = ActiveCell.........

    Example:
    Sub Test_For_Error ()
    Do Unitl IsEmpty(ActiveCell)
    On Error GoSub ErrorHandler
    oTest = ActiveCell.Offset(0,20) 'Test on this variable for an error

    Continue:
    ActiveCell.Offset(1,0).Select
    Loop

    ErrorHandler:
    oLastRow = Worksheets("Errors").Range("A65536").End(xlUp).Row + 1
    Worksheets("Errors").Range("A" & oLastRow) = oTest
    GoSub Continue
    End Sub

    Your thoughts are appreciated.

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ErrorHandler (XP)

    I assume that you have Until instead of Unitl, and that you have GoTo instead of GoSub.
    But even then, your code doesn't make much sense. There is no way the code will ever finish.
    Can you explain what you want to accomplish?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ErrorHandler (XP)

    Hans,

    In my effort to retype the code (I did not copy/paste) I did not catch the typos you mention.

    What I am trying to accomplish is to test for an error on the variable named "oTest". Start at the top of a column and continue looking/looping for errors until an empty cell is found. If an error is encountered, prepare a laundry list of errors in the worksheet named "errors"

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ErrorHandler (XP)

    What kind of error do you expect? This code will not catch error values in cells.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ErrorHandler (XP)

    Hans,

    The variable "oTest " is referencing a table and trying to match the activecell to the table. If it is not found in the table then an error is encountered. As I mention, the first encountered error processes just fine. It is the subsequent errors that hang the code up.

    Regards,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ErrorHandler (XP)

    Are you sure that you posted the correct code (apart from the typos)? As posted, it doesn't do anything except loop forever.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ErrorHandler (XP)

    Let's try this bit of code:

    Sub Test_for_Error()
    Set af = Application.WorksheetFunction
    Do Until IsEmpty(ActiveCell)
    On Error GoTo ErrorHandler

    oTest = af.Match(ActiveCell.Offset(0, 0), Sheets("Tables").Range("My_List"), 0)

    Continue:
    ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub

    ErrorHandler:
    oLastRow = Worksheets("Errors").Range("A65536").End(xlUp).Row + 1
    Worksheets("Errors").Range("A" & oLastRow) = ActiveCell.Offset(0, 0) 'Creates the laundry list
    GoTo Continue
    End Sub


    Sorry for the confusion.
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ErrorHandler (XP)

    That makes a lot more sense!

    Try this version. It avoids selecting cells, and uses Resume Next to continue from where the error occurred.
    I added explicit declarations and changed the names of the variables to match their data types.

    Sub Test_for_Error()
    Dim af As WorksheetFunction
    Dim lngTest As Long
    Dim lngLastRow As Long
    Dim rngCell As Range

    On Error GoTo ErrorHandler

    Set af = Application.WorksheetFunction
    Set rngCell = ActiveCell
    Do Until rngCell = ""
    lngTest = af.Match(rngCell, Sheets("Tables").Range("My_List"), 0)
    Set rngCell = rngCell.Offset(1, 0)
    Loop
    Exit Sub

    ErrorHandler:
    lngLastRow = Worksheets("Errors").Range("A65536").End(xlUp).Row + 1
    Worksheets("Errors").Range("A" & lngLastRow) = rngCell 'Creates the laundry list
    Resume Next
    End Sub

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ErrorHandler (XP)

    Hans,

    I found a resolution at Chip Pearson's website .

    Instead of using "GoTo Continue" in the code I should have used "Resume Continue:".

    Thank you for your time.
    John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ErrorHandler (XP)

    Yes, Resume Continue will work. But you don't even need the Continue: label, you can use Resume Next, as shown in my previous reply.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ErrorHandler (XP)

    Hans,

    I really like your solution where one does not have to "select cell".

    Many thanks,
    John

Posting Permissions

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