On error goto problem

[Coppercrutch]

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 ?

 

[Coppercrutch]

Just worked it out. I will use 'on error resume next' instead of go to. All
works fine.

Cheers anyway.

 

[Chip Pearson]

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)

 

[Dave Peterson]

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.