vba error handling in loop vba error handling in loop vba vba

vba error handling in loop


The problem is probably that you haven't resumed from the first error. You can't throw an error from within an error handler. You should add in a resume statement, something like the following, so VBA no longer thinks you are inside the error handler:

For Each oSheet In ActiveWorkbook.Sheets    On Error GoTo NextSheet:     Set qry = oSheet.ListObjects(1).QueryTable     oCmbBox.AddItem oSheet.NameNextSheet:    Resume NextSheet2NextSheet2:Next oSheet


As a general way to handle error in a loop like your sample code, I would rather use:

on error resume nextfor each...    'do something that might raise an error, then    if err.number <> 0 then         ...    end if next ....


How about:

    For Each oSheet In ActiveWorkbook.Sheets        If oSheet.ListObjects.Count > 0 Then          oCmbBox.AddItem oSheet.Name        End If    Next oSheet