On Error GoTo Issue, please help!

jpvba · Aug 24th 2012

Beginner here! I have this code where it tries to find a certain criteria, but if it doesn't find it I want it to loop back to the beginning and try another criteria. My issue is after it fails once, the On Error GoTo will not work again since it's already on a hard fail (atleast from what I've read). Is there any way to make it loop even if it's already failed once? Also note that if it does pass(IE find the creteria, it goes through a whole process and then loops back to the start hence the Do right after ErrHandler) Below is the code

Code

ErrHandler:
Do
    Sheets("control sheet").Select
    Range("B1").Select
    Selection.End(xlDown).Select
    Selection.Cut
    Range("A1").Select
    ActiveSheet.Paste
ErrorCode = Range("A1")
    Sheets("Raw data").Select
    Columns("AL:AL").Select
    On Error GoTo ErrHandler
    Selection.Find(What:=ErrorCode, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    On Error GoTo ErrHandler

Display More

**norie** · Aug 24th 2012

Re: On Error GoTo Issue, please help!

Do you have a list of criteria you want to go through?

jpvba · Aug 24th 2012

Re: On Error GoTo Issue, please help!

Quote from norie;624707

Do you have a list of criteria you want to go through?

Bascially what I do is have a list of names in B that I paste in so think

Henry
Mark
John
ashley
jim
juan

and sometimes jim will show up and other times his name won't.

cytop · Aug 24th 2012

Re: On Error GoTo Issue, please help!

Just some comments without seeing all your code - you didn't post it alL!

Rather than trying to activate a range directly in the .FIND, assign the results of the FIND to a range variable. You can then check if that range is nothing (in other words, the search term was not found), or not (term found)

For example

Code

Dim r as Excel.Range
..
..
Set r = Selection.Find(What:=ErrorCode, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False)


If r is nothing then
    '// Do something when not found
Else
    r.Activate
    '// Do something else when found
End If

Display More

Considering your code, above, you should also read up on error handling, can't imagine why you have 2 On Error statements pointing to the same error handler... I might suggest this as a starting point.

**royUK** · Aug 24th 2012

Re: On Error GoTo Issue, please help!

You don't need to slect the ranges or sheets.

Code

On Error Goto ErrHandler 
    Selection.Find(What:=ErrorCode, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False).Activate 
    On Error Goto ErrHandler

If you really want to go use an error handler like this then the second on error should release error handling back to Excel

Cytop's suggestion is the way to go.

Code

On Error Goto ErrHandler 
    Selection.Find(What:=ErrorCode, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
    False, SearchFormat:=False).Activate 
    On Error Goto 0

Richie(UK) · Aug 24th 2012

Re: On Error GoTo Issue, please help!

Hi,

You've already had some feedback regarding the general structure of your code so I'll just comment on the error-handling part.

When an error arises it effectively flips a switch so that error mode is 'on'. Whilst in error mode any subsequent errors will not be handled (which is what you were experiencing). The way to switch it 'off' again lies in the use of Resume (or exiting the sub). It's also good practice to have your error-handling routines at the end of a sub.

Compare the following:

Code

Sub To_Err_Is_Human()
    Dim rngCell As Range
    
    On Error GoTo ErrHandler
TheStart:
    MsgBox Err.Number
    Set rngCell = Cells(0, 0)
    
Exit Sub
ErrHandler:
    MsgBox Err.Number
    GoTo TheStart
End Sub


Sub To_Err_Is_Human2()
    Dim rngCell As Range
    
    On Error GoTo ErrHandler
TheStart:
    MsgBox Err.Number
    Set rngCell = Cells(0, 0)
    
Exit Sub
ErrHandler:
    MsgBox Err.Number
    Resume TheStart
End Sub

Display More

**norie** · Aug 25th 2012

Re: On Error GoTo Issue, please help!

Perhaps something like this.

Code

Dim rng As Range
Dim rngFnd As Range
Dim ErrorCode


Set rng = Sheets("control sheet").Range("B1")


While rng.Value <> ""


    ErrorCode = rng.Value


    With Sheets("Raw data")
        Set rngFnd = .Columns("AL:AL").Find(What:=ErrorCode, After:=.Range("AL1"), LookIn:=xlFormulas, _
                                            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                            MatchCase:=False, SearchFormat:=False)
    End With


    If rngFnd Is Nothing Then
        MsgBox ErrorCode & " not found."
    Else
        ' find worked so do whatever
    End If
    ' move on to next name
    Set rng = rng.Offset(1)
Wend

Display More

On Error GoTo Issue, please help!

Participate now!

Share