From Array into Array to Range

Question

I Created a TempTable as ReDim TempTable(1 To RowSize, 1 To 3), for each cell I've a vector of 55 value. See image below to better understand

Now I'd like to copy the entire content of TempTable in my worksheet. The range should be [7576x55, 3] since TempTable has 7576 rows. Is there any smart and fast way to do that?

Create a new 2D array of the appropriate size; then loop through your original array and populate it. Possibly `DIM V(1 to 7576, 1 to 3*55)`. Then set R = R(7576,165) and R.Value = V — Ron Rosenfeld, Dec 17 '15 at 13:36
`Dim tmpTBL as variant: tmpTBL = Range("A1:BC7576").Value2: Debug.Print LBound(tmpTBL, 1) & ":" & UBound(tmpTBL, 1): Debug.Print LBound(tmpTBL, 2) & ":" & UBound(tmpTBL, 2)`. I don't understand what you want to do with the 55 (... or 3?) values. — , Dec 17 '15 at 13:44
@Jeeped I need to paste them into a worksheet. Ideally I need to take TempTable(1,1) copy to A1:A55, then tahe TempTable(1,2) and copy to B1:B5, then TempTable(1,3) to C1:C55. Now shift down and stack so, TempTable(2,1) to A56:A111, TempTable(2,2) to B56:B111, etc. — gmeroni, Dec 17 '15 at 16:54
Both arrays and worksheets can handle 1,048,576 rows. Why the third dimension to the array? — , Dec 17 '15 at 16:59
Since I copy data 55 by 55. Is there a way to assign a selection of 55 to the first 55 rows of an array and not put them in the 3rd dimension? (I used to manage 3d matrix in matlab but was much more easier, so i'm biased) — gmeroni, Dec 17 '15 at 17:04
@R3uK: I tried but wasn't getting the data in the way i liked. I ended up rewrinting the whole process and get a Temp Table of the right dimensions at the first population. See here the code: http://stackoverflow.com/a/34398976/2349516 — gmeroni, Dec 21 '15 at 16:22
@gmeroni : If my code worked for your initial issue, please accept the answer to mark your question as answered! ;) — R3uK, Mar 01 '17 at 10:13

score 1 · Answer 1 · answered Dec 17 '15 at 17:04

This should work smoothly (if the vectors in each column have the same size) :

Sub test_gmeroni()
Dim TempTable() As Variant, _
    wS As Worksheet, _
    Rg As Range, _
    InBound As Long, _
    i As Long, _
    j As Long

Set wS = ThisWorkbook.Sheets("OutPut")
Set Rg = wS.Range("A1")
ReDim TempTable(1 To 7576, 1 To 3)

For i = LBound(TempTable, 1) To UBound(TempTable, 1)
    For j = LBound(TempTable, 2) To UBound(TempTable, 2)
        'Calculate vectors size
        InBound = UBound(TempTable(i, j), 1)
        'Put vector on sheet
        Rg.Offset(0, j - 1).Resize(InBound, 1).Value2 = TempTable(i, j)
    Next j
    'Select next cell to start printing next row
    Set Rg = Rg.Offset(InBound + 1, 0)
Next i

End Sub

From Array into Array to Range

1 Answers1