Why the On Error is skipping the next error in sequence?

[prabha_friend]

Private Sub Consolidate_Click()
Dim temp As Variant
Excel.Application.Visible = True
temp = Dir(CurrentProject.Path & "\Inputs\")
Do While temp <> vbNullString
    Workbooks.Open CurrentProject.Path & "\Inputs\" & temp
    
    For Each Sheet In ActiveWorkbook.Sheets
        Sheet.Activate
        ReDim temp(0) 'Dates
        Set temp(0) = Range(Columns(1).Cells.SpecialCells(xlCellTypeConstants).Cells(1).Offset(1, 0), Columns(1).Cells(Cells.SpecialCells(xlCellTypeLastCell).Row))
        ReDim Preserve temp(1)  'Error_Types
        Set temp(1) = Range(temp(0).Cells(1).Offset(-1, 1), Rows(temp(0).Cells(1).Offset(-1, 1).Row).Cells(Cells.SpecialCells(xlCellTypeLastCell).Column))
        On Error GoTo Error_Handler
        For Each cell In Range(temp(0).Cells(1).Offset(0, 1), Cells.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeConstants, 1)
            DoCmd.SetWarnings (False)
            If temp(0).Cells(1).Row = 4 Then
                DoCmd.RunSQL ("INSERT INTO Errors ( Error_Date, Error_Country_Process, Error_Type, Error_Count ) SELECT #" & Intersect(Rows(cell.Row), temp(0)) & "# AS [Date], Countries_Processes.Country_Process_ID, (SELECT error_type_id FROM error_types WHERE error_type_Name='" & Intersect(temp(1), Columns(cell.Column)) & "') AS Type, " & cell.Value & " AS [Count] FROM Countries INNER JOIN (Processes INNER JOIN Countries_Processes ON Processes.Process_ID = Countries_Processes.Process) ON Countries.Country_ID = Countries_Processes.Country WHERE (((Countries.Country_Code)='" & Intersect(temp(1).Offset(-2, 0), Columns(cell.Column)).MergeArea.Cells(1) & "') AND ((Processes.Process_Name)='" & Intersect(temp(1).Offset(-1, 0), Columns(cell.Column)).MergeArea.Cells(1) & "'));")
            Else
                DoCmd.RunSQL ("INSERT INTO Errors ( Error_Date, Error_Country_Process, Error_Type, Error_Count ) SELECT #" & Intersect(Rows(cell.Row), temp(0)) & "# AS [Date], Countries_Processes.Country_Process_ID, (SELECT error_type_id FROM error_types WHERE error_type_Name='" & Intersect(temp(1), Columns(cell.Column)) & "') AS Type, " & cell.Value & " AS [Count] FROM Countries INNER JOIN (Processes INNER JOIN Countries_Processes ON Processes.Process_ID = Countries_Processes.Process) ON Countries.Country_ID = Countries_Processes.Country WHERE (((Countries.Country_Code)='" & Intersect(temp(1).Offset(-1, 0), Columns(cell.Column)).MergeArea.Cells(1) & "') AND ((Processes.Process_Name)='" & Right(Sheet.Name, Len(Sheet.Name) - InStrRev(Sheet.Name, "-")) & "'));")
            End If
            DoCmd.SetWarnings (True)
        Next cell
Next_Sheet:
    Next Sheet
    temp = Dir
Loop
MsgBox "Done"
Exit Sub
Error_Handler:
If Err.Number = 1004 And Err.Description = "No cells were found." Then GoTo Next_Sheet
End Sub

From the second iteration its not picking the error. I knew a solution for it but as I had a long gap in my career currently couldn't remember it. Please help

 

[Brianwarnock]

I may be wrong but I always thought that the on error was the first statement in a procedure and that you exited the error handling via the Resume statement.

Brian

 

[pr2-eugin]

The Error handler handles Error in this way. On Error statement dictates the compiler what to do when an Error is encountered. In this instance it is asked to go to the Label "Error_Handler".

So the First time the error occurs which could be possibly the Error 1004, the compiler is asked to GoTo Next_Sheet. Mind the Error is still Fresh and Active. In other words the Error is not handled, but asked to be ignored.

Unless you use the word Resume, the compiler is still busy handling the old Error 1004. So when the next time the error happens again, the error is not 1004, but something else, (I am not 100% sure about the error number or description), but that is why the If fails, thus exiting the Sub. You can replace the word GoTo with Resume.

For a very good explanation of how to use Error handler routine in VBA, check out Chip's website - http://www.cpearson.com/Excel/ErrorHandling.htm

....that you exited the error handling via the Resume statement.

Just added verbiage to Brian's post.

 

[prabha_friend]

Thanks Paul. and Brian too.

If Err.Number = 1004 And Err.Description = "No cells were found." Then Resume Next_Sheet

works fine

Thank you both. Is vbaInet on Leave? just curious...

 

[Brianwarnock]

Just added verbiage to Brian's post.

Useful explanatory verbiage


Brian

 

[vbaInet]

Thank you both. Is vbaInet on Leave? just curious...

He's around prabha

By the way, may I add the following?
1. Explicit references objXl.ActiveWorkbook.Sheets. I've mentioned it before. Yes it's working now but I can assure you that one day it will fail if you don't use explicitly reference. This is common programming practice.
2. You can use a SELECT..CASE statement in the error handling label to handle individual error numbers.
3. The Redim/Redim Preserve temp(x) aren't really necessary. Just Redim the variable as (1) before the loop and reuse it within the loop.
4. Use Currentdb.Execute in place of DoCmd.RunSQL to avoid the need of turning off and on warnings. However, even if you still want to use RunSQL then at least turn it back on in your error handler.

Just my 2 cents.