excelvbaloopsif-statementvlookup

Excel VBA - Skip sub or end sub IF Statement


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?


Solution

  • You should use:

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

    Instead of:

    Else
        End Sub '<-- Instead of End
    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