Error 91

[David]

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,
--
David

[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.

--
Regards,
Tom Ogilvy

"David" <David@discussions.microsoft.com> wrote in message
news:D8D5D77A-6CF6-4931-B98E-A4F90892FABC@microsoft.com...
> 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,
> --
> David

[David]

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

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "David" <David@discussions.microsoft.com> wrote in message
> news:D8D5D77A-6CF6-4931-B98E-A4F90892FABC@microsoft.com...
> > 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,
> > --
> > David
>
>
>