Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |