transform array of varying lengths

[MetLife]

Hi,

I have been fiddling with this logic, but I can't seem to make it work.

Basically I just want to transpose arrays with varying lengths using the Ubound property.

Here is the original code:

Set rng = Range("VBAOutput")
rng.offset(0, 229).Value = Application.Transpose(array)

THANKS

 

[tonyyy]

MetLife,

The following might get you started...

Sub testArr()
Dim rng As Range, r As Long, c As Long
Dim arr As Variant
Set rng = Range("VBAOutput")
arr = rng.Value

r = UBound(arr)
c = UBound(arr, 2)
Range(Cells(rng.Row, rng.Column), Cells(c, r)).Offset(0, 229).Value = Application.Transpose(arr)
End Sub

Cheers,

tonyyy

 

[mikerickson]

If your array is 2-dimentional, code like this will work.

Set rng = Range("VBAOutput")

With rng
    .Resize(UBound(someArray,2), UBound(someArray,1)).Value = Application.Transpose(someArray)
End With

If someArray is a one dimensional array then

Rng.Resize(Ubound(someArray),1).Value = Application.Transpose(someArray)

BTW, Array is a reserved word in VBA and can't be used as a variable name.

 

[shg]

Another way:

Sub testArr()
  With Range("VBAOutput")
    .Offset(229).Resize(.Columns.Count, .Rows.Count).Value2 = Application.Transpose(.Value2)
  End With
End Sub

 

[Rick Rothstein]

If someArray is a one dimensional array then

Rng.Resize(Ubound(someArray),1).Value = Application.Transpose(someArray)

It might depend on how the one-dimensional array came into being. If the Split function was used, or if the Array function was used with the Option Base defaulted to or set to 0, then you would have to use UBound(someArray)+1 inside the Resize property call.

 

[mikerickson]

Good catch. If I remember correctly the full expression should be UBound(someArray) + (1 - LBound(someArray))

 

[MetLife]

Thanks for your help guys!