On Error in Loop 3

[OzzieTheOwl]

Hi

The code below is looking for a matach from one sheet to another, works fine when there is a match, but when there is no match it gives me a Run Time Error 91. I understand this is because there is no match, but I thought the "On Error" line would sort that out. Have I done something obviously wrong?

Any help greatly appreciated.

    For APlantLoop = 2 To APlantLastRow
        APlantFleetNum = Sheets("A-Plant").Range("G" & APlantLoop)
        APlantDesc = Sheets("A-Plant").Range("J" & APlantLoop)
        If Sheets("A-Plant").Range("L" & APlantLoop) = "SALES" Then GoTo APlantEnd
        Sheets("BBUL").Select
        Range("A1").Select
        On Error GoTo NoAPlantMatch
        Cells.Find(What:=APlantFleetNum, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        BBULRow = ActiveCell.Row
        BBULFleetNum = Sheets("BBUL").Range("D" & BBULRow)
        BBULDesc = Sheets("BBUL").Range("C" & BBULRow)
        If BBULFleetNum = APlantFleetNum Then APlantCheck = 1
NoAPlantMatch:
    'Code Here
APlantEnd:
        APlantCheck = 0
    Next APlantLoop

 

[TonyJollans]

The problem is that once you have had a single error you go into 'error mode' and stay there which means that any further error is handled by default handling. Once you have trapped an error, you must have a Resume statement to get back into 'trapping mode'.

The simplest thing with your code as is is to add a line:

...
NoAPlantMatch:
    [green]'Code Here[/green]
    [blue]Resume APlantEnd[/blue]
APlantEnd:

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[TomCologne]

How should
"NoAPlantMatch:
'Code Here"
handle the error?

Try this:

NoAPlantMatch:
If Err.Number = 91 Then Resume Next

This will still break if an error other than 91 occurs.

TomCologne

 

[stevexff]

The Find method returns Nothing if it can't find a match. Because you've appended an Activate method on the end, it can't 'Activate' Nothing. Set a Range variable to the Find result. Then you can check to see if the returned range is Nothing, and act accordingly.

This will also allow you to replace your GoTos with conditionals, which is generally considered to be A Good Thing^TM

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[OzzieTheOwl]

TonyJollans and TomCologne

Thanks to both of you, used the resume and the Err.Number to get the result I required. Now works perfectly.

stevexff

Never thought of using that, thanks for the pointer. I will re-write the code using that method.

Thanks to you all for your prompt responses.