Excel VBA: On Error Goto statement not working inside For-Loop

With the code as shown, you're actually still considered to be within the error handling routine when you strike the next statement.

That means that subsequent error handlers are not allowed until you resume from the current one.

A better architecture would be:

    Dim myCol As ListColumn    For Each myCol In myTable.ListColumns        On Error GoTo ErrCol        Dim myDate As Date        myDate = CDate(myCol.Name)        On Error GoTo 0        ' MORE CODE HERE 'NextCol:    Next myCol    Exit Sub ' or something 'ErrCol:    Resume NextCol

This clearly delineates error handling from regular code and ensures that the currently executing error handler finishes before you try to set up another handler.

This site has a good description of the problem:

Error Handling Blocks And On Error Goto

An error handling block, also called an error handler, is a section of code to which execution is tranferred via a On Error Goto <label>: statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can't use the On Error Goto <label>: statement merely skip over lines. For example, the following code will not work properly:

    On Error GoTo Err1:    Debug.Print 1 / 0    ' more codeErr1:    On Error GoTo Err2:    Debug.Print 1 / 0    ' more codeErr2:

When the first error is raised, execution transfers to the line following Err1:. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.

You need to add resume of some sorts in your error handling code to indicate the error handling is over. Otherwise, the first error handler is still active and you are never "resolved."

See http://www.cpearson.com/excel/errorhandling.htm (specifically the heading "Error Handling Blocks And On Error Goto" and following section)

Follow-up to paxdiablo's accepted answer. This is possible, allowing two error traps in the same sub, one after the other :

Public Sub test()    On Error GoTo Err1:    Debug.Print 1 / 0    ' more codeErr1:    On Error GoTo -1     ' clears the active error handler    On Error GoTo Err2:  ' .. so we can set up another    Debug.Print 1 / 0    ' more codeErr2:    MsgBox "Got here safely"End Sub

Using On Error GoTo -1 cancels the active error handler and allows another to be set up (and err.clear doesn't do this!). Whether this is a good idea or not is left as an exercise for the reader, but it works!