On Error GoTo : How to use multiple?

E

e18

I wan't to use multiple On Error GoTo commands, but If one error appear
the next error results in Run-time error 91. Do I have to rese
something after each check (...and in case, what?)?

Thanks, Erlend


Dim test1, test2, test3

Sub checkk()

test1 = False
test2 = False
test3 = False

check1:
On Error GoTo check2
Columns("B").Find(What:="length").Activate
test1 = True

check2:
On Error GoTo check3
tab1start = Columns("A").Find(What:="md").Row
test2 = True

check3:
On Error GoTo stopp
tab2start = Columns("B").Find(What:="east").Row
test3 = True

stopp:
If Not test3 Then
MsgBox "Unknown format"
Exit Sub
End If

End Su
 
E

e18

I could of course use *On Error Resume Next* (tab1start and tab2star
are equal to zero if Columns.Find not found) as a workaround, but
still would very much like an answer to how to have more than one *O
Error GoTo* in a macro.

Thank you.

Erlen
 
T

Tom Ogilvy

Dim rng1 as Range, rng2 as Range
set rng1 = Columns(1).Find("Start")

set rng2 = Columns(1).Find("End")

if not rng1 is nothing then
msgbox "Start at " & rng1.row
End if
if not rng2 is nothing then
msgbox "End at " & rng2.row
End if

This doesn't raise an error if the search term isn't found.
 
A

Alan Beban

Tom Ogilvy provided some code to avoid your problem. Nevertheless, I
have inserted some code in your originally provided code to illustrate
one way to have multiple On Error statements executed. You need to
resume execution (i.e., exit the error handler) after entering each
error handler.

Alan Beban
I wan't to use multiple On Error GoTo commands, but If one error appear,
the next error results in Run-time error 91. Do I have to reset
something after each check (...and in case, what?)?

Thanks, Erlend


Dim test1, test2, test3

Sub checkk()
Dim test1, test2, test3
test1 = False
test2 = False
test3 = False

'check1:
On Error GoTo check2
Columns("B").Find(What:="length").Activate
test1 = True

check2:
Resume insert2
insert2:Err.Clear
On Error GoTo check3
tab1start = Columns("A").Find(What:="md").Row
test2 = True

check3:
Resume insert3
insert3:Err.Clear
 
S

Stephen Bullen

Hi Alan,

Unfortunately, you can't sprinkle the Resume statements in the middle of the
code like that. If you want to use multiple error checks, you need to have
the error handlers at the bottom of the procedure to ensure the code doesn't
enter the error handler if there isn't an error:

Sub checkk()

Dim test1 As Boolean
Dim test2 As Boolean
Dim test3 As Boolean

check1:
On Error GoTo ErrCheck1
Columns("B").Find(What:="length").Activate
test1 = True

check2:
On Error GoTo ErrCheck2
tab1start = Columns("A").Find(What:="md").Row
test2 = True

check3:
On Error GoTo ErrCheck3
tab2start = Columns("B").Find(What:="east").Row
test3 = True

stopp:
If Not test3 Then
MsgBox "Unknown format"
End If

Exit Sub

'These are the error handlers for each test
ErrCheck1:
Resume check2

ErrCheck2:
Resume check3

ErrCheck3:
Resume stopp

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie
 
A

Alan Beban

Are you sure? It seemed to work as I posted it with "length" in Column
B, thus test1 = true.

Alan Beban
 
R

Rob Bovey

Hi Alan,

Your procedure appears to work, but that's just a coincidence of the way
you structured it. If you place a watch on the Err object and single-step
through the code, you'll notice that any Resume statement you hit that
wasn't reached as a result of an error actually causes an error itself (Err
20: Resume without error).

Because of the way you set up the error handling blocks, however, this
error is simply caught by the error handler defined above it. The Resume
statement is then executed a second time as a result of the error, which is
OK, and it causes execution to resume at the specified line label.

To see the problem, try putting "length" and "east" in column B and "md"
in column A. Then comment out all of your On Error GoTo statements. If the
code was structured correctly, it should run without error, because the Find
method locates what it's looking for in every instance. However, you'll see
that the code actually bombs when it hits the first Resume statement. Under
the same conditions, Stephen's code will execute correctly.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
A

Alan Beban

Thanks, Rob. I guess the exercise for me now is to figure out the
circumstances in which my erroneous approach will actually cause a
problem. The way the error handling blocks were set up was not just
happenstance.

Thanks again,
Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top