How to skip over block of code once an errors occurs?

[zohaib]

Hello,

What I am trying to do is that when excel encounters an error i would like it to move to another step and so on. I tried using the on error goto and resume next but it was not working the way I want it to. Thanks for your help in advance.

Sub test()

'step1
      'block of code
     
      'if error occurs in the line above than _
      skip the lines below and goto STEP2.

      'mulitiple lines of code
      'mulitiple lines of code
      'mulitiple lines of code

'step2
      'block of code
     
      'if error occurs in the line above than _
      skip the lines below and goto STEP3.
     
      'mulitiple lines of code
      'mulitiple lines of code
      'mulitiple lines of code
     
     
'step3
      'block of code
     
      'if error occurs in the line above than _
      skip the lines below and goto STEP4.
     
      'mulitiple lines of code
      'mulitiple lines of code
      'mulitiple lines of code
     


'step4

      'block of code
     
      'if error occurs in the line above than _
      skip the lines below and goto STEP5.
     
      'mulitiple lines of code
      'mulitiple lines of code
      'mulitiple lines of code
     

'step5
      'block of code
     
      'if error occurs in the line above than _
      skip the lines below.
     
      'mulitiple lines of code
      'mulitiple lines of code
      'mulitiple lines of code
     
     


End Sub

 

[Logit]

Your code should work with a slight change. Here is the version with the GoTo commands:

Option Explicit

Sub test()
Dim Sheets As Sheet1
Dim Range As Range
Dim Value As String

On Error GoTo step2

'step1
     'block of code
   
          'if error occurs in the line above than _
      skip the lines below and goto STEP2.
     
      'mulitiple lines of code
     'mulitiple lines of code
     'mulitiple lines of code

step2:
On Error GoTo step3

     'block of code
   
      'if error occurs in the line above than _
      skip the lines below and goto STEP3.
         
      'mulitiple lines of code
     'mulitiple lines of code
     'mulitiple lines of code
   
step3:
On Error GoTo step4

     'block of code
   
      'if error occurs in the line above than _
      skip the lines below and goto STEP4.
     
   
   
      'mulitiple lines of code
     'mulitiple lines of code
     'mulitiple lines of code
   


step4:
On Error GoTo step5

      'block of code
   
      'if error occurs in the line above than _
      skip the lines below and goto STEP5.
         
      'mulitiple lines of code
     'mulitiple lines of code
     'mulitiple lines of code
   
step5:
On Error GoTo step6
   
     'block of code
   
      'if error occurs in the line above than _
      skip the lines below.
   
   
      'mulitiple lines of code
     'mulitiple lines of code
     'mulitiple lines of code

step6:
Exit Sub

End Sub

 

[NARAYANK991]

Hi ,

See if the following link helps :

https://excelmacromastery.com/vba-error-handling/

Narayan

 

[zohaib]

Hello,

I want to thank both of you. The code Logit provided was what I was using but I was getting stuck on step 2. It would not go pass step 2 due to step 1 error handler. The article provided help me find the missing piece. I need to clear error handler in step1 in order for step2 to work and so on. The "On Error GoTo -1" helps to get out of step1 error handler and get ready to hand another error. Here is the code in case someone else is looking for something like this. thanks again.

Sub test()
Dim Sheets As Sheet1
Dim Range As Range
Dim Value As String

'ActiveSheet.Range("A1").Value = "test"

On Error GoTo step2

'fyi sheet does not exist. this is to create an error on purpose
Worksheets("Sheet11").Select


step2:
On Error GoTo -1
On Error GoTo step3

'fyi sheet does not exist. this is to create an error on purpose
    Worksheets("Sheet12").Select
  


step3:
On Error GoTo -1
On Error GoTo step4


'fyi sheet does not exist. this is to create an error on purpose
Worksheets("Sheet13").Select



step4:
On Error GoTo -1
On Error GoTo step5


'fyi sheet does not exist. this is to create an error on purpose
Worksheets("Sheet14").Select


step5:
On Error GoTo -1
On Error GoTo step6


'fyi sheet does not exist. this is to create an error on purpose
Worksheets("Sheet15").Select

step6:
On Error GoTo -1
Exit Sub

End Sub

 

[Debaser]

IMO, if you are using On Error Goto -1, then your code is in dire need of restructuring. The fact that you also have multiple Goto statements would tend to support that idea as well.

 

[zohaib]

Hello Debaser,

I know but the code I am using this for is pivot tables. If sheet1 has data then my code continues to make the pivot table but when sheet1 is empty it moves to step2 which is other pivot table based on another sheet. so the code runs in series to create pivot tables for sheets that have data only. I also would not recommend this for any other application but this gets the work done in the mean time. I have isolated this particular code to making pivot tables only so I can keep an eye on it in case something were to go wrong. Again I agree so many goto code would indeed need to be tweaked. This was my 1st error handler vba project and I need a quick fix.

 

[Debaser]

It sounds very much as though you should be refactoring this into a generic routine that creates a pivot table from a sheet, and then call that routine for each sheet you need to process. Then you would not require any of the Goto statements, and probably not even an error handler in the refactored routine.

 

[zohaib]

ahh I see. I don't know why I didn't think of that. Thanks so much