What is the perfect solution for the run-time error '1004' No Cells were found?

[prabha_friend]

<!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --><!-- END TEMPLATE: ad_showthread_firstpost_start -->Here I am getting it:
For Each cell In Range(temp(0).Cells(1).Offset(0, 1), Cells.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeConstants, 1)

 

[ranman256]

1 of your objects is not defined. Either temp doesnt exist, or SpecialCells did not resolve.
Try a more simpler range method, like
ActiveSheet.Range("a1").CurrentRegion.Select

or validate what you selected
Range(temp(0)).select

 

[prabha_friend]

Actually this error is popping only when there are no numerical-constant cells i.e SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeConstants, 1). What is the perfection to tackle this?

 

[Peter_SSs]

I'm not really sure what you are doing with that double Specialcells bit but this sort of structure should do it.

Dim rSpecial As Range, cell As Range

On Error Resume Next
Set rSpecial = Range(temp(0).Cells(1).Offset(0, 1), Cells.SpecialCells(xlCellTypeLastCell)).SpecialCells(xlCellTypeConstants, 1)
On Error GoTo 0
If Not rSpecial Is Nothing Then
  For Each cell In rSpecial
  
  Next cell
End If

 

[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?

 

[Peter_SSs]

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?

I don't see a particular advantage over the method I suggested, but by all means give it a go if you are more comfortable with that approach.