Populate 1D Array from 2D range Using Cell Property

[rkaczano]

I have a 2d Range in Excel that I want to pass to a 1D array in VBA. I want to build the 1d Array based on incrementing through each column of each row. The code I have is working but it is building the array based on incrementing the rows of each column. I want the opposite but cannot figure out how to edit the code.

2, 4, 6, 8,
3, 6, 9, 12
5, 10,15, 20

What I want:
2
4
6
8
3
6
9
12

What my code is doing:
2
3
5
4
6
10

Option Explicit
Sub Convert()
Dim Vector
Dim k As Integer
Vector = Create_Vector(Sheets("Sheet1").Range("A4:D8"))
For k = 1 To UBound(Vector)
        Sheets("Sheet1").Range("B20").Offset(k, 1).Value = Vector(k)
Next k
End Sub
*****************
Function Create_Vector(Matrix_Range As Range) As Variant
Dim No_of_Cols As Integer, No_Of_Rows As Integer
Dim i As Integer
Dim j As Integer
Dim Cell
No_of_Cols = Matrix_Range.Columns.Count
No_Of_Rows = Matrix_Range.Rows.Count
ReDim Temp_Array(No_of_Cols * No_Of_Rows)
'Eliminate NULL Conditions
If Matrix_Range Is Nothing Then Exit Function
If No_of_Cols = 0 Then Exit Function
If No_Of_Rows = 0 Then Exit Function

For j = 1 To No_Of_Rows
    For i = 0 To No_of_Cols - 1
    Temp_Array((i * No_Of_Rows) + j) = Matrix_Range.Cells(j, i + 1) 'was Temp_Array((i * No_Of_Rows) + j) = Matrix_Range.Cells(j, i + 1)
    Next i
Next j
Create_Vector = Temp_Array
End Function

Thanks

 

[Peter_SSs]

When posting code, please use code tags - see my signature block below for details. I have fixed your post for you this time.

See if altering the double loop in your function to this does what you want.

For j = 0 To No_Of_Rows - 1
    For i = 1 To No_of_Cols
    Temp_Array((j * No_of_Cols) + i) = Matrix_Range.Cells(j + 1, i)
    Next i
Next j

 

[rkaczano]

Thank-you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.