Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VBA on error resume next

  1. #1
    New Member
    Join Date
    Mar 2009
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA on error resume next

    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!

  2. #2
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA on error resume next

    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 by Richard Schollar; Feb 18th, 2011 at 12:07 PM. Reason: corrected typo
    Richard Schollar

    Using xl2013

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,749
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA on error resume next

    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 by shg; Feb 18th, 2011 at 12:09 PM.

  4. #4
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,749
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA on error resume next

    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

  5. #5
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA on error resume next

    Quote Originally Posted by shg4421 View Post
    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 by Richard Schollar; Feb 18th, 2011 at 12:20 PM.
    Richard Schollar

    Using xl2013

  6. #6
    New Member
    Join Date
    Mar 2009
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA on error resume next

    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

  7. #7
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA on error resume next

    Have you used Shg's suggestions?
    Richard Schollar

    Using xl2013

  8. #8
    New Member
    Join Date
    Mar 2009
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA on error resume next

    Yes this one works!!!

    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

  9. #9
    New Member
    Join Date
    Mar 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA on error resume next

    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?

  10. #10
    Board Regular
    Join Date
    Feb 2011
    Location
    Columbus, OH
    Posts
    125
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA on error resume next

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •