Run-time error '1004': Application-defined or object-defined error when trying to define the Range

Question

I see this error when I try my code:

Run-time error '1004': Application-defined or object-defined error

My code:

Sub search(date1, month, sheet, index)
    Dim cell As Range
    Dim startDate As Integer
    Dim finalIndex As Integer 
    Dim letra1 As Single
    Dim letra2 As Single

    Dim textoPlano As Integer
    Dim svcPoliza As Integer
    Dim svcMarcas As Integer
    Dim svcDptos As Integer
    Dim svcCotizacionesCme As Integer
    Dim svcAniosVehiculo As Integer
    Dim svcRiesgoVigente As Integer
    Dim svcLineasVehiculos As Integer
    Dim svcLeeLocalidades As Integer

    Dim hoja As Worksheet
    Dim ultimaFila As Long
    Dim resultado As Worksheet

    Set resultado = ActiveWorkbook.Worksheets("Resultados")
    Set hoja = ActiveWorkbook.Worksheets("Sheet" & sheet)

    If sheet = "Consolidado" Then
        Set hoja = ActiveWorkbook.Worksheets("Consolidado")
    End If

    ultimaFila = hoja.Cells(hoja.Rows.Count, "G").End(xlUp).Row

    For Each cell In Range("G3:G" & ultimaFila)
        If InStr(cell.Value, "enviarCorreoTextoPlano") > 0 Then
            textoPlano = textoPlano + 1
        End If
        If InStr(cell.Value, "svcPolizaRecienteVehiculo") > 0 Then
            svcPoliza = svcPoliza + 1
        End If
        If InStr(cell.Value, "svcMarcasVehiculos") > 0 Then
            svcMarcas = svcMarcas + 1
        End If
        If InStr(cell.Value, "svcLeeDptos") > 0 Then
            svcDptos = svcDptos + 1
        End If
        If InStr(cell.Value, "svcLeeCotizacionesCme") > 0 Then
            svcCotizacionesCme = svcCotizacionesCme + 1
        End If
        If InStr(cell.Value, "svcLeeAniosVehiculo") > 0 Then
            svcAniosVehiculo = svcAniosVehiculo + 1
        End If
        If InStr(cell.Value, "svcRiesgoVigenteVehiculo") > 0 Then
            svcRiesgoVigente = svcRiesgoVigente + 1
        End If
        If InStr(cell.Value, "svcLineasVehiculos") > 0 Then
            svcLineasVehiculos = svcLineasVehiculos + 1
        End If
        If InStr(cell.Value, "svcLeeLocalidades") > 0 Then
            svcLeeLocalidades = svcLeeLocalidades + 1
        End If
    Next cell

    If index = 1 Then
        letra1 = 1
        letra2 = 2
        startDate = date1
    End If

    If index = 2 Then
        letra1 = 3
        letra2 = 4
        startDate = date1 + 1
    End If

    If index = 3 Then
        letra1 = 5
        letra2 = 6
        startDate = date1 + 2
    End If

    If index = 4 Then
        letra1 = 7
        letra2 = 8
        startDate = date1 + 3
    End If

    If index = 5 Then
        letra1 = 9
        letra2 = 10
        startDate = date1 + 4
    End If

    resultado.Cells(1, letra1).Value = "Dia " & startDate & "-" & month
    resultado.Cells(2, letra1).Value = "enviarCorreoTextoPlano"
    resultado.Cells(3, letra1).Value = "svcPolizaRecienteVehiculo"
    resultado.Cells(4, letra1).Value = "svcMarcasVehiculos"
    resultado.Cells(5, letra1).Value = "svcLeeDptos"
    resultado.Cells(6, letra1).Value = "svcLeeCotizacionesCme"
    resultado.Cells(7, letra1).Value = "svcLeeAniosVehiculo"
    resultado.Cells(8, letra1).Value = "svcRiesgoVigenteVehiculo"
    resultado.Cells(9, letra1).Value = "svcLineasVehiculos"
    resultado.Cells(10, letra1).Value = "svcLeeLocalidades"
    resultado.Cells(2, letra2).Value = textoPlano
    resultado.Cells(3, letra2).Value = svcPoliza
    resultado.Cells(4, letra2).Value = svcMarcas
    resultado.Cells(5, letra2).Value = svcDptos
    resultado.Cells(6, letra2).Value = svcCotizacionesCme
    resultado.Cells(7, letra2).Value = svcAniosVehiculo
    resultado.Cells(8, letra2).Value = svcRiesgoVigente
    resultado.Cells(9, letra2).Value = svcLineasVehiculos
    resultado.Cells(10, letra2).Value = svcLeeLocalidades`enter code here`
End Sub

The Debugger shows this line:

resultado.Cells(1, letra1).Value = "Dia " & startDate & "-" & month

I first detected this error when I had Range and not Cells method to define the cell. Then I changed it to Cells method but the same error appeared.

Are you certain that ActiveWorkbook at the time that line is being executed, is, indeed, the workbook containing Worksheets(Resultados) ? Perhaps if you defined the workbook by name, or by ThisWorkbook. — Ron Rosenfeld
Maybe put a Debug.Print line in before the error line, that checks the value of letra1, startDate and month. — CLR
Also, with regard to startDate, you've declared it as an Integer. That will cause problems with dates after 16-Sep-1989. Declare it as Long or Double. — Ron Rosenfeld
Thank you so much boys for the quick response :). The problem was index hasn't value. — Eldest

rmbradburn rmbradburn · Accepted Answer · 2020-01-08T18:54:28

It could be related to "index" being out of range. In this case, "letra1" and "letra2" will be Null, so resultado.Cells(1,letra1) will result in run-time error 1004. I tested this and found it to be true.

To avoid this, you could add one more If as a catch-all:

If index < 1 Or index > 5 Then
    MsgBox "Invalid index"
    Exit Sub
End If

...or to simplify the whole selection process, consider replacing all of the If statements with Select Case:

Select Case index
    Case 1
        letra1 = 1
        letra2 = 2
        startDate = date1
    Case 2
        letra1 = 3
        letra2 = 4
        startDate = date1 + 1
    Case 3
        letra1 = 5
        letra2 = 6
        startDate = date1 + 2
    Case 4
        letra1 = 7
        letra2 = 8
        startDate = date1 + 3
    Case 5
        letra1 = 9
        letra2 = 10
        startDate = date1 + 4
    Case Else
        MsgBox "Invalid index"
        Exit Sub
End Select

EDIT

A simpler solution may be to just calculate "letra1", "letra2", and "startdate" without all of the if statements, just verifying first that "index" is not less than 1:

If index < 1 Then
    MsgBox "Invalid index"
    GoTo DoneHere
End If

letra1 = index + (index - 1)
letra2 = letra1 + 1
startDate = date1 + (index - 1)

Run-time error '1004': Application-defined or object-defined error when trying to define the Range

1 Answers