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:
Click to expand...
Resume insert2
insert2:Err.Clear
On Error GoTo check3
tab1start = Columns("A").Find(What:="md").Row
test2 = True

check3:
Click to expand...
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
 
Top