[RESOLVED] "On Error" not catching all errors

[Chrissy]

I have an Excel macro where I am selecting a worksheet in a workbook, and if the worksheet does not exist, it is supposed to go to the next one. I am doing this by using the On Error statement.

I have this statement in the begining of the sub.

On Error GoTo OverHere

Then I have the following further down in the code.

OverHere:

It seems to only work for the first error, but doesn't catch any after that. The code stops running and a debug message box pops up.

Does anyone know why it is only catching the first instance of the error?

[si_the_geek]

It's because you aren't exiting the error handler properly - you need to have some kind of Resume.

Show us your code, and we'll explain how to do it for your situation.

[Chrissy]

Here is my code....

Sub DistribAnalysis()

Dim FsSearch As Office.FileSearch
Dim vaFileName As Variant

Dim xBook As String
Dim NewBook As String
Dim xSheet As String

Dim xRow As Integer
Dim oRow As Integer
Dim iRow As Integer

Dim xTemp(7) As String
Dim xProp As String

Dim xCol As Integer

NewBook = Workbooks.Add.Name
Columns("A:A").Select
Selection.NumberFormat = "@"
Rows("9:9").Select
Selection.NumberFormat = "@"

Workbooks.Open ThisWorkbook.Path + "/Indemnification.xls", False, True

Application.DisplayAlerts = False
Set FsSearch = Application.FileSearch

xRow = 12
xCol = 3

With FsSearch
    .NewSearch
    .LookIn = ThisWorkbook.Path + "\odl input\"
    .Filename = "*.xls"
    .SearchSubFolders = True
    .FileType = msoFileTypeExcelWorkbooks
    .LastModified = msoLastModifiedAnyTime
    .Execute  'Count of how many records are found

    For Each vaFileName In .FoundFiles
        Workbooks.Open vaFileName, False, True
        xBook = ActiveWorkbook.Name
        
        oRow = 12
        
        Do While Cells(oRow, 1) <> ""
        
            xTemp(0) = Cells(oRow, 1)
            xTemp(1) = Cells(oRow, 2)
            xTemp(2) = Cells(oRow, 5)
            
            Workbooks("indemnification.xls").Activate
            On Error GoTo OverHere
            Sheets(xTemp(1)).Select
        
            iRow = 9
            
            Do While Cells(iRow, 1) <> ""
                xTemp(3) = Cells(iRow, 1)
                xTemp(4) = Cells(iRow, 2)
                'indemnification percentage
                xTemp(5) = Cells(iRow, Left(Sheet1.cmbMonth, 2) + 3)
                
                'xTemp(6) = Cells(iRow, Left(Sheet1.cmbMonth, 2) + 3)
                
                Workbooks(NewBook).Activate
                xRow = 12
                
                Do While Cells(xRow, 1) <> ""
                    If Cells(xRow, 1) = xTemp(3) Then
                        Exit Do
                    End If
                    
                    If Cells(xRow, 1) > xTemp(3) Then
                        Rows(xRow).Insert xlShiftDown
                        Exit Do
                    End If
                    
                    xRow = xRow + 1
                    
                Loop
                
                Cells(9, xCol) = xTemp(1)
                Cells(10, xCol) = xTemp(0)
                
                Cells(8, xCol) = xTemp(2)
                
                Cells(xRow, 1) = xTemp(3)
                Cells(xRow, 2) = xTemp(4)
                Cells(xRow, xCol) = "= " + Format(xTemp(5)) + " * R[-" + Format(xRow - 8) + "]C"
                
                
                Workbooks("indemnification.xls").Activate
                iRow = iRow + 1
            
            Loop
            xCol = xCol + 1
OverHere:
            
            Workbooks(xBook).Activate
            oRow = oRow + 1
        Loop
        Workbooks(xBook).Close False
        
    Next vaFileName
    
End With

End Sub

[si_the_geek]

Ah.. you haven't actually got an error handler at all, and to be honest there isn't a good reason for using one for what you are doing - you should just check if there is a sheet with that name, eg:

            Workbooks("indemnification.xls").Activate

Dim intCount As Integer, booFound As Boolean
            booFound = False
            For intCount = 1 To Workbooks("indemnification.xls").Sheets.Count
              If Sheets(intCount).Name = xTemp(1) Then
                booFound = True
                Exit For
              End If
            Next intCount
            If booFound Then

              Sheets(xTemp(1)).Select
              iRow = 9
...
              Loop
              xCol = xCol + 1

            End If

            Workbooks(xBook).Activate

Error handling is a good idea (especially for dealing with unexpected errors), but it needs to be used appropriately. For an explanation & examples, see the article about it in our Classic VB FAQs (link in my signature).

[Chrissy]

I checked your link, and I still don't understand why it only works for the first error and not the rest of them. Can you briefly explain?

The workbook I am checking has over 100 tabs in it and would have be checked over 100 times. It wouldn't be very efficient to check the whole workbook every time. I tried to find a way to see if the tab exists in the workbook without checking the whole book every time. This was the most effiecient way I could think of.

[si_the_geek]

It because you are not using an error handler - you are just using "On Error" to jump to another part of the code, which is not appropriate.

As soon as an error occurs, error handling stops, and it does not start again until you exit the error handler properly. An error handler needs to somehow deal with the error (eg: by showing a message, or by doing some kind of "correction"), and then return to the code - which is the part that turns error handling back on.

If you look at the examples in the FAQ, all of them "leave" the code to go to a separate error handler, which does something and then returns to the code - you cannot simply jump to another part of your routine.


You may think that using my suggestion isn't very efficient, but it isn't much less efficient that using an error handler (which is "bad practice", and makes your code harder to read & maintain).

As the file "indemnification.xls" is open throughout the whole routine, you you could make the checking more efficient by storing the names of all sheets to a string (separated by a delimiter) before the loop starts, and then using Instr to check if the name you are looking for (with the delimiter on either side) exists in the string.

[Chrissy]

Ah.....I get it now... Thank you.

Thanks for the good idea. I will save all the tab names in a string and search the string to see if the tab exits. Thanks!