Output 2d Aray to Range verticaly

[JamesW]

Hey,

I have a 2d array (dynamic number of "rows" and 5 "columns").

My array is populated as follows (This has been simplified, the actual population is much more complex):

Sub foo()
    Dim lRow As Long, i As Long, x As Long
    Dim matVendor() As String
    lRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
    x = 0
    For i = 1 To lRow
        With Sheets(2).Cells(i, 1)
            If .Value = "x" Then
                x = x + 1
                ReDim Preserve matVendor(1 To 5, 1 To x)
                
                matVendor(1, x) = .Offset(-1, 0).Value
                matVendor(2, x) = .Offset(3, 0).Value
                matVendor(3, x) = .Offset(6, 2).Value
                matVendor(4, x) = .Offset(6, 3).Value
                matVendor(5, x) = .Offset(5).Value
            End If
        End With
    Next i
    Erase matVendor()
    i = Empty
    lRow = Empty
    x = Empty
End Sub

I want to output the contents of this array into a spreadsheet vertically (I used this, which output horizontaly - I couldn't figure out how to transpose it) so that it looks like this:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[/TR]
[TR]
[TD]matVendor(1,1)
[/TD]
[TD]matVendor(2,1)
[/TD]
[TD]matVendor(3,1)
[/TD]
[TD]matVendor(4,1)
[/TD]
[TD]matVendor(5,1)
[/TD]
[/TR]
[TR]
[TD]matVendor(1,2)
[/TD]
[TD]matVendor(2,2)
[/TD]
[TD]matVendor(3,2)
[/TD]
[TD]matVendor(4,2)
[/TD]
[TD]matVendor(5,2)
[/TD]
[/TR]
[TR]
[TD]matVendor(1,3)
[/TD]
[TD]matVendor(2,3)
[/TD]
[TD]matVendor(3,3)
[/TD]
[TD]matVendor(4,3)
[/TD]
[TD]matVendor(5,3)
[/TD]
[/TR]
[TR]
[TD]matVendor(1,4)
[/TD]
[TD]matVendor(2,4)
[/TD]
[TD]matVendor(3,4)
[/TD]
[TD]matVendor(4,4)
[/TD]
[TD]matVendor(5,4)
[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas?

Cheers,

James

 

[RoryA]

Depending on the data and array size, you may simply be able to use Application.Transpose(matVendor) to swap it round.

 

[pgc01]

Hi

Something like:

'...
ReDim matVendor(1 To 5, 1 To x)

'...
Range("A1").Resize(UBound(matVendor, 2), UBound(matVendor, 1)).Value = Application.Transpose(matVendor)

 

[JamesW]

Well, that was simple! I was way over thinking this solution... Thanks guys.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

 

[JamesW]

I'm glad it helped. Thanks for the feedback.

Question, why did you omit Preserve? Was I incorrect in using Redim Preserve?

 

[pgc01]

Question, why did you omit Preserve? Was I incorrect in using Redim Preserve?

No, I was just illustrating the use of the Transpose. You should keep the Preserve.

Remark:

The syntax is correct, but I would not use this. Redim is a heavy operation, it is bad practice to use it in a loop.

The way I would do it is to ReDim the array to the max number of elements at the beginning and redim is to the correct size at the end.

Ex., using your code:

Sub foo()
    Dim lRow As Long, i As Long, x As Long
    Dim matVendor() As String

    lRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
    ReDim matVendor(1 To 5, 1 To lRow)
    x = 0
    For i = 1 To lRow
        With Sheets(2).Cells(i, 1)
            If .Value = "x" Then
                x = x + 1

                matVendor(1, x) = .Offset(-1, 0).Value
                matVendor(2, x) = .Offset(3, 0).Value
                matVendor(3, x) = .Offset(6, 2).Value
                matVendor(4, x) = .Offset(6, 3).Value
                matVendor(5, x) = .Offset(5).Value
            End If
        End With
    Next i
    ReDim Preserve matVendor(1 To 5, 1 To x)

'...

Notice that I only used Redim 2 times, at the beginning, I ReDimmed the array with the maximum number of positions. After the loop, I know how many positions I need and I adjust the size of the array.

Notice that, as I said, your code should also work, it's just less efficient.

 

[RoryA]

You can also use an array of arrays, then you don't need to worry about transposing it because you can Redim Preserve the container array. On the other hand, you'll need Index to dump it to the worksheet in one hit.

 

[JamesW]

Notice that I only used Redim 2 times, at the beginning, I ReDimmed the array with the maximum number of positions. After the loop, I know how many positions I need and I adjust the size of the array.

Notice that, as I said, your code should also work, it's just less efficient.

Thanks PGC. This will come in handy in future. Learnt a lot today

Rory - I'm only just coming to grips with Arrays - Jagged Arrays are something I'm looking forward to learning about (NOT).

 

[RoryA]

That's not really jagged, as they all have the same dimensions.