Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

So there are several SO questions and Google results that come up under "On Error GoTo executes once" and in just about every case the recommended solution is to add Err.Clear or some forum of Resume to clear the error out. VBA errors can only be handled one at a time, so they need to be cleared.

Having implemented these, as you might have guessed, I am running into this issue where the On Error GoTo is only executing once and I can't figure out why.

Below is my loop. I did leave some code off the top because there is quite a bit of it and it isn't relevant. Mostly user prompts and making arrays. To explain a little what is going on, conos() is an array containing the values of a specific column. Based on a segment of the filename, it searches for the code in the array, to get its index, which corresponds to the row.

If there isn't a Match it triggers the error. That just means there is a file, but no contact to send it to. It should skip to NoContact and create a list of these files.

So with my files, the first has a contact and generates the email, the second does not and skips to NoContact and adds the file to the list. Five more run with contacts and then it gets to another that should go to NoContact , but Unable to get the Match property of the WorksheetFunction class comes up.

It seems the error isn't getting cleared from the first one. Not sure why.

For Each objFile In objFolder.Files
    wbName = objFile.Name
    ' Get the cono along with handling for different extensions
    wbName = Replace(wbName, ".xlsx", "")
    wbName = Replace(wbName, ".xlsm", "")
    wbName = Replace(wbName, ".xls", "")
    ' Split to get just the cono
    fileName() = Split(wbName, "_")
    cono = fileName(2)
    ' Create the cell look up
    c = Cells(1, WorksheetFunction.Match("Cono", cols(), 0)).Column
    ' ******************** ISSUE IS HERE ***************************
    On Error GoTo NoContact
    r = Cells(WorksheetFunction.Match(cono, conos(), 0), c).Row
    Cells(r, c).Select
    ' Fill the variables
    email = Cells(r, c).Offset(0, 1).Value
    firstName = Cells(r, c).Offset(0, 3).Value
    lastName = Cells(r, c).Offset(0, 4).Value
    account = Cells(r, c).Offset(0, -2).Value
    username = Cells(r, c).Offset(0, 6).Value
    password = Cells(r, c).Offset(0, 7).Value
    fPassword = Cells(r, c).Offset(0, 8).Value
    ' Mark as completed
    Cells(r, c).Offset(0, 9).Value = "X"
    ' Set the object variables
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    ' Body of the email
    str = "Hi " & firstName & "," & vbNewLine & vbNewLine & _
          "This is line 1" & vbNewLine & _
          "This is line 2" & vbNewLine & _
          "This is line 3" & vbNewLine & _
          "This is line 4"
    ' Parameters of the email
    On Error Resume Next
    With OutMail
        .To = email
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = str
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
    End With
    On Error GoTo 0
    ' Based on the user prompts, whether or not the emails will be sent without checking them first
    If finalCheck = vbYes Then
        OutMail.Send
        OutMail.Display
    End If
NoContact:
    ' Determiine which files don't have a corresponding email and add to list
    If email = Empty Then
        If conoB <> "" Then
            conoB = conoB & ", " & cono
            conoB = cono
        End If
    End If
    Err.Clear
    ' Clear variables for next use
    Set OutMail = Nothing
    Set OutApp = Nothing
    cono = Empty
    email = Empty
    firstName = Empty
    lastName = Empty
    account = Empty
    username = Empty
    password = Empty
    fPassword = Empty
Next:
                Not addressing your problems with error handling, but you can avoid the whole issue by using Application.Match() and not WorksheetFunction.Match().  The latter will throw a runtime error if no match is found, whereas the former returns an error value which you can test for using IsError() - it is much easier to manage that than catching runtime errors.
– Tim Williams
                Mar 3, 2017 at 6:51
                YowE3K is spot on with his answer on why you are still running into the issue. Just worth pointing out the 'Exit Sub' added in his code above the error handling block whereas your's doesn't have this. Without Exit Sub, your code will run the error handler at the end even if here isnt an error. This can cause it's own error.
– Brandon Barney
                Mar 3, 2017 at 12:48
                @BrandonBarney - The OP couldn't have an Exit Sub before the NoContact label because they wanted the remainder of the code to be executed before going through the next iteration of the loop.
– YowE3K
                Mar 3, 2017 at 19:06
                @YowE3K I should have clarified I meant the NoContactError label which is where you have the 'Exit Sub' included. I wasn't saying your revision was wrong, I was just noting it for the OP since 'Exit Sub' should be included above any error handlers.
– Brandon Barney
                Mar 3, 2017 at 19:19
                @BrandonBarney LOL - my comment must have also been unclear - I was trying to say that the OP's code relied on the code below the "error-handler" label being executed in all cases, not just in error situations.  So I completely agree that "'Exit Sub' should be included above any error handlers" and the OP's problem arose because they were trying to treat On Error GoTo as if it was a standard GoTo.
– YowE3K
                Mar 3, 2017 at 19:34
Err.Clear just clears the information regarding the last error from the Err object - it does not exit out of error handling mode.
If an error is detected and your On Error GoTo NoContact is invoked, your code jumps down to the NoContact label, and then finally finds it way back to the start of your For Each objFile In objFolder.Files loop while still in error-handling mode.
If another error occurs while still in error-handling mode, VBA throws the error as it can no longer trap it.
You should structure your code along the lines of 
    For Each objFile In objFolder.Files
        On Error GoTo NoContactError
NoContact:
    Exit Sub
NoContactError:
    'Error handling goes here if you want it
    Resume NoContact
End Sub
                @sockpuppet - I am very pleased that you went with Tim's suggestion - it is what I would have done to avoid using On Error in the first place.
– YowE3K
                Mar 3, 2017 at 19:03
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.