CODEWITHSUNDEEP
excelvba
Error 1004
Error 1004

Reputation: 8220

Type mismatch error on importing values to an array

I m initializing an array and import values from a specific range. if the range are greater than one line the code works fine BUT in case the range is just one line i m getting an error of:

Run time error 13: Type mismatch

Code:

Sub test()

    Dim arr As Variant
    Dim i As Long, LastRow As Long

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        arr = .Range("A1:A" & LastRow)

        For i = LBound(arr) To UBound(arr)

        Next i

    End With

End Sub

Any help will appreciated.

After @JvdV answer i manage to overcome this problem but i face other one:

when there is more than one lines the array looks like that:

enter image description here

But when there is only one line:

enter image description here

How to create the same array structure - dimensions?

Upvotes: 0

Views: 92

Answers (1)

JvdV
JvdV

Reputation: 75870

With just a single cell you won't have an array. Turn on local variables to check this behaviour.

What you can do for example is to check if arr actually is an array or check the value of LastRow. For example:

Sub test()

Dim arr As Variant
Dim i As Long, lr As Long

With ThisWorkbook.Worksheets("Sheet1")

    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    arr = Range("A1:A" & lr)

    If lr = 1 Then ReDim arr(1 To 1, 1 To 1): arr(1,1) = .Cells(1, 1)

    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i,1)
    Next i

End With

End Sub

Upvotes: 1

Related Questions