Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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. -- Regards, Tom Ogilvy "e18 " wrote in message ... I could of course use *On Error Resume Next* (tab1start and tab2start are equal to zero if Columns.Find not found) as a workaround, but I still would very much like an answer to how to have more than one *On Error GoTo* in a macro. Thank you. Erlend --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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 e18 < wrote: 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 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 Sub --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
Are you sure? It seemed to work as I posted it with "length" in Column
B, thus test1 = true. Alan Beban Stephen Bullen wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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 * "Alan Beban" wrote in message ... Are you sure? It seemed to work as I posted it with "length" in Column B, thus test1 = true. Alan Beban Stephen Bullen wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error GoTo : How to use multiple?
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 Rob Bovey wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
On error from Find, GoTo E | Excel Discussion (Misc queries) | |||
On error goto problem | Excel Discussion (Misc queries) | |||
On Error GoTo Doesn't Work | Excel Discussion (Misc queries) | |||
On error goto 0? | Excel Programming | |||
On error goto 0 | Excel Programming |