Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
On error goto problem
I have the following code:
On Error GoTo cont6: Cells.Find(What:="IP Trinity", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate cont6: I have used this because when the required info is not found I was getting a runtime '91' error message - Object variable or with block variable not set? Now the error goto works fine and dandy, but only if there is one error ? I have this error go to in 7 seperate bits of the same sub as I am looking for 7 different things -and it only works the first time it is required ? On the second error it gives the previous 91 message. Is there a simple reason this will only work once in the same sub and cannot be repeated ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
On error goto problem
Just worked it out. I will use 'on error resume next' instead of go to. All
works fine. Cheers anyway. "Coppercrutch" wrote: I have the following code: On Error GoTo cont6: Cells.Find(What:="IP Trinity", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate cont6: I have used this because when the required info is not found I was getting a runtime '91' error message - Object variable or with block variable not set? Now the error goto works fine and dandy, but only if there is one error ? I have this error go to in 7 seperate bits of the same sub as I am looking for 7 different things -and it only works the first time it is required ? On the second error it gives the previous 91 message. Is there a simple reason this will only work once in the same sub and cannot be repeated ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
On error goto problem
Now the error goto works fine and dandy, but only if there is one error ? The reason for this is that VBA can operate in two "modes". Nearly all the time, it operates in "normal" mode. However, when an trappable error occurs and is handled by an "On Error Goto <Label" statement, execution switches to "error" mode. In this case, no other errors can be trapped until an "Exit Sub/Function/Property" or a "Resume" or a "Resume Next" statement is executed. No errors can be trapped, regardless of any "On Error" statement, when execution is in error mode. The code must either exit the procedure or execute a "Resume" or "Resume Next" statement in order to switch back to normal mode and allow for subsequent error handling. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Coppercrutch" wrote in message ... I have the following code: On Error GoTo cont6: Cells.Find(What:="IP Trinity", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate cont6: I have used this because when the required info is not found I was getting a runtime '91' error message - Object variable or with block variable not set? Now the error goto works fine and dandy, but only if there is one error ? I have this error go to in 7 seperate bits of the same sub as I am looking for 7 different things -and it only works the first time it is required ? On the second error it gives the previous 91 message. Is there a simple reason this will only work once in the same sub and cannot be repeated ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
On error goto problem
You could also check to see if your string were found first, then if it was
found, do the .activate: dim FoundCell as Range set foundcell = cells.find(what:="IP Trinity", ...rest of find here) if foundcell is nothing then msgbox "Not found" else foundcell.activate end if And drop the "on error" stuff completely. Coppercrutch wrote: I have the following code: On Error GoTo cont6: Cells.Find(What:="IP Trinity", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate cont6: I have used this because when the required info is not found I was getting a runtime '91' error message - Object variable or with block variable not set? Now the error goto works fine and dandy, but only if there is one error ? I have this error go to in 7 seperate bits of the same sub as I am looking for 7 different things -and it only works the first time it is required ? On the second error it gives the previous 91 message. Is there a simple reason this will only work once in the same sub and cannot be repeated ? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On Error GoTo Doesn't Work | Excel Discussion (Misc queries) | |||
Question on "On Error GoTo skip" | Excel Discussion (Misc queries) | |||
On Error GoTo Label in a loop only working once. | Charts and Charting in Excel | |||
On Error GoTo skip needs help | Excel Discussion (Misc queries) | |||
Goto Data validation Same ERROR | Excel Discussion (Misc queries) |