How to tackle the run-time error '1004' No Cells were found?

[prabha_friend]

Here I am getting it:

If Not (Range(temp(0).Cells(1).Offset(0, 1), Cells.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeConstants, 1) Is Nothing) Then
            For Each cell In Range(temp(0).Cells(1).Offset(0, 1), Cells.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeConstants, 1)

 

[Brianwarnock]

No cells in the range contain numbers
Cells formatted as text but containing numbers do not qualify as numbers.

Brian

 

[prabha_friend]

Thanks Peter. I followed your suggestion and implemented the following code:

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

The above code solves the purpose. I just want to put an else in the Error_Handler doing whatever it does without the on_error block. Possible?
Like this: If Err.Number = 1004 And Err.Description = "No cells were found." Then GoTo Next_Sheet else Run_as_it_is. Possible?

 

[Brianwarnock]

Replied in your new thread which I read first.

Brian