CODEWITHSUNDEEP
excelvba
manuel martin
manuel martin

Reputation: 21

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

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!

Upvotes: 1

Views: 97

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 1

Related Questions