Array output help...

[Juggler_IN]

I have code to print a 3d array as 2d array to a range. With the sample test array the output is:

1	3	5
7	9	11
13	15	17
2	4	6
8	10	12
14	16	18

But, my requirement is:

1	2	3
4	5	6
7	8	9
10	11	12
13	14	15
16	17	18

I have not been able to figure out the modification. Any help is appreciated.

Sub Print3DArrayToRange(arr As Variant, ws As Worksheet, startCell As Range)
    Dim x As Long, y As Long, z As Long
    Dim rowOffset As Long
    For z = LBound(arr, 3) To UBound(arr, 3)
        rowOffset = (z - LBound(arr, 3)) * (UBound(arr, 1) - LBound(arr, 1) + 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            For x = LBound(arr, 1) To UBound(arr, 1)
                ws.Cells(startCell.Row + rowOffset + x - LBound(arr, 1), startCell.Column + y - LBound(arr, 2)) = arr(x, y, z)
            Next x
        Next y
    Next z
End Sub
Sub Test()
    ReDim x(2&, 2&, 1&) As Integer
    x(0&, 0&, 0&) = 1&
    x(0&, 0&, 1&) = 2&
    x(0&, 1&, 0&) = 3&
    x(0&, 1&, 1&) = 4&
    x(0&, 2&, 0&) = 5&
    x(0&, 2&, 1&) = 6&
    ' ---
    x(1&, 0&, 0&) = 7&
    x(1&, 0&, 1&) = 8&
    x(1&, 1&, 0&) = 9&
    x(1&, 1&, 1&) = 10&
    x(1&, 2&, 0&) = 11&
    x(1&, 2&, 1&) = 12&
    ' ---
    x(2&, 0&, 0&) = 13&
    x(2&, 0&, 1&) = 14&
    x(2&, 1&, 0&) = 15&
    x(2&, 1&, 1&) = 16&
    x(2&, 2&, 0&) = 17&
    x(2&, 2&, 1&) = 18&

    Print3DArrayToRange x, Sheets("Output"), Range("L15")

End Sub

 

[Micron]

Did you step through that and watch your variables? On the 2nd pass, arr(x, y, z) is 1,0,0 -
which you have defined as 7 --->> x(1&, 0&, 0&) = 7& and you are offsetting by rows so 7 comes underneath 1. Probably the easiest would be to rearrange your array? Changing your offset to columns first won't fix it.

 

[Micron]

This debug.print would show you what order arr(x,y,z) is being used
1
7
13
3
9
15
5
11
17
2
8
14
4
10
16
6
12
18

 

[Micron]

Here's a more robust output in case it helps.
Iteration: 1 - 0,0,0 Array: 1
Iteration: 2 - 1,0,0 Array: 7
Iteration: 3 - 2,0,0 Array: 13
Iteration: 4 - 0,1,0 Array: 3
Iteration: 5 - 1,1,0 Array: 9
Iteration: 6 - 2,1,0 Array: 15
Iteration: 7 - 0,2,0 Array: 5
Iteration: 8 - 1,2,0 Array: 11
Iteration: 9 - 2,2,0 Array: 17
Iteration: 10 - 0,0,1 Array: 2
Iteration: 11 - 1,0,1 Array: 8
Iteration: 12 - 2,0,1 Array: 14
Iteration: 13 - 0,1,1 Array: 4
Iteration: 14 - 1,1,1 Array: 10
Iteration: 15 - 2,1,1 Array: 16
Iteration: 16 - 0,2,1 Array: 6
Iteration: 17 - 1,2,1 Array: 12
Iteration: 18 - 2,2,1 Array: 18

 

[DanteAmor]

Hi @Juggler_IN. Thanks for posting on the forum.

The third array is stored 2 by 2. So it is recommended to read all the values and arrange them 3 by 3.
That is, read the values, store the first three values in the first row, read another three values and put them in the next row, and so on.

The code could be like this:

Sub Print3DArrayToRange(arr As Variant, ws As Worksheet, startCell As Range)
  Dim x As Long, y As Long, z As Long, i As Long, j As Long
  ReDim a(1 To ((UBound(arr, 1) + 1) * (UBound(arr, 2) + 1) * (UBound(arr, 3) + 1)) / 3, 1 To 3)
  i = 1
  j = 1
  For x = LBound(arr, 1) To UBound(arr, 1)
    For y = LBound(arr, 2) To UBound(arr, 2)
      For z = LBound(arr, 3) To UBound(arr, 3)
        a(i, j) = arr(x, y, z)
        j = j + 1
        If j = 4 Then
          j = 1
          i = i + 1
        End If
      Next
    Next
  Next
  startCell.Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Sub Test()
  ReDim x(2&, 2&, 1&) As Integer
  x(0&, 0&, 0&) = 1&
  x(0&, 0&, 1&) = 2&
  x(0&, 1&, 0&) = 3&
  x(0&, 1&, 1&) = 4&
  x(0&, 2&, 0&) = 5&
  x(0&, 2&, 1&) = 6&
  ' ---
  x(1&, 0&, 0&) = 7&
  x(1&, 0&, 1&) = 8&
  x(1&, 1&, 0&) = 9&
  x(1&, 1&, 1&) = 10&
  x(1&, 2&, 0&) = 11&
  x(1&, 2&, 1&) = 12&
  ' ---
  x(2&, 0&, 0&) = 13&
  x(2&, 0&, 1&) = 14&
  x(2&, 1&, 0&) = 15&
  x(2&, 1&, 1&) = 16&
  x(2&, 2&, 0&) = 17&
  x(2&, 2&, 1&) = 18&
 
  Call Print3DArrayToRange(x, Sheets("Output"), Range("L15"))
End Sub

Output:

Dante Amor
	A											L	M	N
1
15												1	2	3
16												4	5	6
17												7	8	9
18												10	11	12
19												13	14	15
20												16	17	18
Output

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Juggler_IN]

@DanteAmor ; Thanks ... I am testing it in my code ... will update.

 

[Juggler_IN]

@Micron ; Thanks for your input.

 

[Micron]

Unfortunately, my grasp of arrays is like a 2 out of 10 and I don't expect that to change much. I wish I could have been of more help because that would mean I could rank myself higher.