Error 91

[Guest]

Hi Group,
I have a workbook with 3 worksheets in it. The first wokrsheet (Comparison)
has values in it that are looked up in the second workseet (Master). It is
looked up using

******
Selection.Find(What:=(Whse), After:=ActiveCell, LookIn:=xlFormulas, LookAt
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False,
SearchFormat:=False).Activate
******

I expect the lookup not to find the Lookup value sometimes and have an
ErrorHandler routine.

****
On Error GoTo ErrorHandler
****

The ErrorHandler routine is a case statement and the error is 91:

****
Select Case Err.Number
Case 91
Other suff.....
End Select
Err.Clear <----This clears the error
****
All is fine to this point, then I get to the line below, again, via a loop
******
Selection.Find(What:=(Whse), After:=ActiveCell, LookIn:=xlFormulas, LookAt
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= False,
SearchFormat:=False).Activate
******
This generates the same Error, 91, but it fails to go to the ErrorHandler. I
have Dim the variables, but I have not Set the variables. It works on the
first pass, but fails on the second pass of the loop and does not go to the
ErrorHandler routine.

Thanks all,

 

[Tom Ogilvy]

You don't have a resume statement - so your error handler is never
completed. When you have an error in the error handler, Excel quits - this
is the situation you have with you second error since you are still in error
handling mode. See Excel VBA help on the Resume statement.

 

[Guest]

Thanks Tom. Thought the Clear Error did that. I will try it out.