VBA on error resume next

br0nc0boy

New Member
Joined
Mar 12, 2009
Messages
27
Code:
Sub Sample()

For i = 7 To [Count]
On Error Resume Next
    Workbooks.Open (Cells(i, 1).Value)
If Err.Number <> 0 Then
Err.Clear
End If
On Error GoTo 0

'code when there's no error

Next i

End Sub

How can I add to my macro so that when it has an error it will bypass and go
to "Next i" and not the next code in line? Thanks!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi

Try:

Code:
Sub Sample()

Dim i As Long
Dim wb As Workbook

For i = 7 To [Count]
On Error Resume Next
   Set wb =  Workbooks.Open(Cells(i, 1).Value)
  If Not wb Is Nothing Then
    On Error GoTo 0

    'code when there's no error
  End If
Next i

End Sub
 
Last edited:
Upvote 0
Try this.

Code:
Option Explicit
 
Sub Sample()
    Dim i As Long
 
    For i = 7 To Range("Count").Value
        On Error Resume Next
        Workbooks.Open Cells(i, 1).Text
 
        If Err.Number <> 0 Then
            Err.Clear
        Else
            On Error GoTo 0
            'code when there's no error
 
        End If
    Next i
End Sub
EDIT: Richard, I think your code will not detect a missing workbook if the wb variable was previously assigned?
 
Last edited:
Upvote 0
Another way:

Code:
Option Explicit
 
Sub Sample()
    Dim i As Long
 
    For i = 7 To Range("Count").Value
        If Len(Dir(Cells(i, "A").Text)) Then
            Workbooks.Open Cells(i, 1).Text
            ' carry on ...
        End If
    Next i
End Sub
 
Upvote 0
EDIT: Richard, I think your code will not detect a missing workbook if the wb variable was previously assigned?

I'm assuming the code (that is currently missing) must close the workbook (big assumption I know) in which case I would expect the wb variable to be cleared ready to be reused.


EDIT: Although having just tested it it seems this isn't the case. Easy enough to correct by setting the wb to Nothing before the next iteration. Interesting though.
 
Last edited:
Upvote 0
Hi Rich,

What it does is, it opens the workbook, go to a certain tab, copy the tab and bring it back to the 1st workbook where i ran the macro from and paste it in the appropriate tab (tab names are number by day Sheets(CStr(i)) and then close the workbook, and activate the 1st workbook once again so that code can go to the Next i. This part is a success...when the workbook exist.

The workbooks that i am opening is downloaded daily and dated but sometimes system failure or ppl forget, so what i can do is go into my reference workbook and delete the cells for the date that wasn't downloaded but i just wanted to learn more VBA.

Thanks,
Br0nc0boy
 
Upvote 0
Yes this one works!!! :biggrin:

Thanks Shg!!!

Code:
Sub Sample()
    Dim i As Long
 
    For i = 7 To Range("Count").Value
        On Error Resume Next
        Workbooks.Open Cells(i, 1).Text
 
        If Err.Number <> 0 Then
            Err.Clear
        Else
            On Error GoTo 0
            'code when there's no error
 
        End If
    Next i
End Sub
 
Upvote 0
Hello experts, I need help
can't understand where to put part for error in my long VBA code.

I have repeated VBA code forex:
"For Each cell In Worksheets" bla bla bla

and afther that is super super long code

in this code sometimes I can have error, and I would like, if error happen, to delete actual sheet and again start with next cell?

any help?
 
Upvote 0
Need to start a new thread. This one is six years old and probably has few of the posters still available.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top