VBA Difference arr(x,x) and arr(x)(x)?-CodePudding

currently i need to assemble two excel columns in this form

Redim arr (1 To 2, 1 To 7000)

example of the result I want

so I went about it like this

Sub main()
    Sheets("data").Activate
    endRow = Cells(Rows.Count, 1).End(xlUp).Row

    ReDim arr(1 To 2)

    arr(1) = Application.Transpose(Range("A2:A" & endRow).value)
    arr(2) = Application.Transpose(Range("H2:H" & endRow).value)

End Sub

but when I do that I get this kind of array with which we access it like this arr (x)(x) and not arr (x, x), what is the difference between these two arrays? and how to get an array arr (x, x) ? the method below don't work and this is the first time I see a table like this

Sub main()
    Sheets("data").Activate
    EndRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    ReDim arr(1 To 2, 1 To EndRow)
    
    arr(1) = Application.Transpose(Range("A2:A" & EndRow).value)
    arr(2) = Application.Transpose(Range("H2:H" & EndRow).value)
End Sub

Error : the index does not belong to the selection

CodePudding user response：

You are loading an array with two separate 1 dimensional arrays and not a single two dimensional array. This is called a jagged array.

When you do this you need note the item location by first which item in the large single dimension array then the item location in the second single dimension array, or arr(1)(1).

The first is saying I want the array in the location 1 and the second the item in that array in location 1.

To get the 2d array like you want I would start with two 2d arrays and loop to put in a third:

Sub main()
    Dim endRow As Long
    endRow = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim aArr As Variant
    aArr = Worksheets("data").Range("A2:A" & endRow).Value
    
    Dim hArr As Variant
    hArr = Worksheets("data").Range("H2:H" & endRow).Value
    
    Dim arr As Variant
    ReDim arr(1 To 2, 1 To UBound(aArr, 1))
    
    Dim i As Long
    For i = 1 To UBound(aArr, 1)
        arr(1, i) = aArr(i, 1)
        arr(2, i) = hArr(i, 1)
    Next i
    
    Debug.Print arr(1, 1)


End Sub