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

Question

I have written to a code that can help me to process the data further. But I dont know why I cant select the cells. I get an error

Run-time error '1004':

Application-defined or object-defined error

Please view my code below

Sub CommandButton1_Click()
Dim day_a, day_b As Date, point As String, east_a, north_a, height_a, height_b, east_b, north_b As Double
Dim i1, i2, i3, i4, i5, i6, i7 As Variant

    i1 = 1: i2 = 1

    Worksheets("INPUT").Cells(i1, i2).Select

    Do While Not IsEmpty(ActiveCell)
        Worksheets("INPUT").Cells(i1, i2).Activate
        If ActiveCell.Value = "Id" Then
            i3 = Split(ActiveCell.Address(columnAbsolute:=True, ReferenceStyle:=xlR1C1), "C")(1)
            MsgBox (cellidrow)
        ElseIf ActiveCell.Value = "Nord" Then
            i4 = Split(ActiveCell.Address(columnAbsolute:=True, ReferenceStyle:=xlR1C1), "C")(1)
        ElseIf ActiveCell.Value = "Øst" Then
            i5 = Split(ActiveCell.Address(columnAbsolute:=True, ReferenceStyle:=xlR1C1), "C")(1)
        ElseIf ActiveCell.Value = "S_OBJID" Then
            i6 = Split(ActiveCell.Address(columnAbsolute:=True, ReferenceStyle:=xlR1C1), "C")(1)
        ElseIf ActiveCell.Value = "DATO" Then
            i7 = Split(ActiveCell.Address(columnAbsolute:=True, ReferenceStyle:=xlR1C1), "C")(1)
        Else
        End If

        i2 = i2 + 1
    Loop

    MsgBox (i3 & i4 & i5 & i6 & i7)

    Sheets("INPUT").Cells(i5, i3).Select
    MsgBox (ActiveCell.Value)

End Sub

Thanks a lot for your help!

Note that in your declaration Dim statement, any variable that is not followed by as var_type will be declared as being of type Variant. So in your first Dim line, day_b, point and north_b will be of type Date, String and Double. All of the other variables in that line will be of type Variant. This may not be what you want. — Ron Rosenfeld

user3598756 user3598756 · Accepted Answer · 2017-02-05T15:57:35

it's because Cells() accepts numbers as its row index and column index parameters, while having declared i5 and i3 as variant and assigned them a value from a Split() function, they become String typed value

you should then convert them to Long as follows

i3 = CLng(Split(ActiveCell.Address(columnAbsolute:=True, ReferenceStyle:=xlR1C1), "C")(1))

but you could refactor your code as follows

Option Explicit

Sub CommandButton1_Click()
    Dim i3 As Long, i4 As Long, i5 As Long, i6 As Long, i7 As Long
    Dim cell As Range

    With Worksheets("INPUT")
        For Each cell In .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))
            Select Case cell.Value
                Case "Id"
                    i3 = cell.Column
                Case "Nord"
                    i4 = cell.Column
                Case "Øst"
                    i5 = cell.Column
                Case "S_OBJID"
                    i6 = cell.Column
                Case "DATO"
                    i7 = cell.Column
                Case Else
            End Select
        Next
        MsgBox (i3 & i4 & i5 & i6 & i7)
        .Cells(i5, i3).Select
        MsgBox (ActiveCell.Value)
    End With
End Sub

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

1 Answers