Excel VBA - Skip sub or end sub IF Statement

Jackson5 picture Jackson5 · Nov 20, 2014 · Viewed 69.9k times · Source

My code loops through cell values in a table and sees if document with same name exists. If it does exist, it will perform an action to open those files and import data.

If bProcess Then
    FileCounter = 0
    For Each folderIDX In PrimaryMergeFiles
        'If folderIDX.Name = Worksheets("Table").Range("A1:A13") Then
        Dim vTest As Variant
        vTest = Application.WorksheetFunction.VLookup(folderIDX.Name, Worksheets("Table").Range("A1:B13"), 2, False)

        'Creating Merge File
        If Not IsError(vTest) Then
            FileCounter = FileCounter + 1

            strStatus = "Creating file " & FileCounter & " of " & PrimaryMergeFiles.Count & ": " & folderIDX.Name
            Application.StatusBar = strStatus
            CreateMergedFile wdApp, sPrimaryMergeDirectory, folderIDX.Name, sSourceFile, ClientCount, _
                sClientSubDirectory, bClearHighlightings(ClientCount), bHome
            'ElseIf IsError(vTest) Then
        Else
            End Sub
        End If  
    Next
End If

How can I skip the files or end the loop/sub when vTest is Error?

Answer

user1274820 picture user1274820 · Nov 20, 2014

You should use:

Else
    Exit Sub '<-- Exit! :)
End If

Instead of:

Else
    End Sub '<-- Instead of End :3
End If

Hope this helps!

Edit: To answer your comment

Dim vTest As Variant

On Error Resume Next '<-- Add these since you are catching the error immediately after
vTest = Application.WorksheetFunction.VLookup(folderIDX.Name, Worksheets("Table").Range("A1:B13"), 2, False)
On Error Goto 0      '<-- You will handle your error on the next line

If Not IsError(vTest) Then '...

'You may also want to use/use instead: If Err.Number <> 0 Then ...

Here is some more information on error handling in VBA: http://www.cpearson.com/excel/errorhandling.htm