How do I combine multiple 1D arrays and create a table (rows, columns) like a 2D array using a loop?
Of course, this loop will not work, as I am missing so many corrections and logic.
I do not want to save this to the tables and use crosstab queries.
I am trying to create a table like 2D array:
Year 2020 2021
Purchase 1000 1500
Sales 3000 3500
Expences 500 1000
And, I don't want to explicitly assign the values to each element. In my real project, that won't be a practical thing to do.
Please help. I really appreciate your knowledge and time.
Code:
Public Function GetCombineArray() As Variant
Dim ColA() As Variant, Row1() As Variant, Row2() As Variant, Row3() As Variant
Dim NewTable() As Variant, RowInputs() As Variant
Dim iC As Integer, jC As Integer
ReDim ColA(2)
ColA(0) = "Year"
ColA(1) = 2020
ColA(2) = 2021
ReDim Row1(2)
Row1(0) = "Purchase"
Row1(1) = 1000
Row1(2) = 1500
ReDim Row2(2)
Row2(0) = "Sales"
Row2(1) = 3000
Row2(2) = 3500
ReDim Row3(2)
Row3(0) = "Expences"
Row3(1) = 500
Row3(2) = 1000
ReDim NewTable(0 To 3, 0 To 2)
'NewTable(0, 0) = ColA(0)
'NewTable(0, 1) = ColA(1)
'NewTable(0, 2) = ColA(2)
'NewTable(0, 3) = ColA(3)'.....
RowInputs = Array(ColA, Row1, Row2, Row3)
For iC = 0 To 3
For jC = 0 To 2
NewTable = RowInputs(iC, jC)
Next jC
Next iC
GetCombineArray = NewTable
Of course, this loop will not work, as I am missing so many corrections and logic.
I do not want to save this to the tables and use crosstab queries.
I am trying to create a table like 2D array:
Year 2020 2021
Purchase 1000 1500
Sales 3000 3500
Expences 500 1000
And, I don't want to explicitly assign the values to each element. In my real project, that won't be a practical thing to do.
Please help. I really appreciate your knowledge and time.