Resize and Transpose question.

[Ken Johnson]

Range("A2").Resize(22,4) represents the range A223.
I've got a 4 row by 22 column variant array (vaNeedToBuy) and I was
expecting to be able to get it into place on the worksheet as a 22 row
by 4 column range using Resize and Transpose like this...

Me.Range("A2").Resize(UBound(vaNeedToBuy, 2), UBound(vaNeedToBuy,
1)).Value _
= WorksheetFunction.Transpose(vaNeedToBuy)

However, instead, the range occupied by the data is B323 with the
final row and final column missing. To get all the data I've used
vaNeedToBuy+1 in both UBound's

To get the data correctly in place I've used four steps...

1. Place it on the sheet using ....

Me.Range("A2").Resize(UBound(vaNeedToBuy, 2) + 1, _
UBound(vaNeedToBuy, 1) + 1).Value _
= WorksheetFunction.Transpose(vaNeedToBuy)

2. Use a new variant array to store the transposed data...

vaNeedToBuy2 = Range("B3:E" & UBound(vaNeedToBuy, 2) + 2)

3. Clear the transposed data off the sheet...

Range("B3:E" & UBound(vaNeedToBuy2, 1) + 2).ClearContents

4. Place the transposed data in place from the new variant array...

Me.Range("A2").Resize(UBound(vaNeedToBuy2, 1), _
UBound(vaNeedToBuy2, 2)).Value = vaNeedToBuy2


I had no luck with Cut Destination:= etc with .Offset(-1,-1) at the
end.

Is the original problem caused by the Worksheetfunction.Transpose?
Is there any easier solution to getting the transposed array correctly
in place?

Ken Johnson

 

[Tom Ogilvy]

sounds like an Option Base problem - your array as actually

vaNeedtoBuy(0 to 4, 0 to 22).

in any event, this works regardless of the option base, but will use 1
additional column if you actually have 5 rows rather than 4 as you believe.

Private Sub CommandButton1_Click()
Dim vaNeedToBuy As Variant
vaNeedToBuy = Worksheets("Sheet2").Range("A2").Resize(4, 22)
Me.Range("A2").Resize(UBound(vaNeedToBuy, 2) - LBound(vaNeedToBuy, 2) + 1,
UBound(vaNeedToBuy, 1) - LBound(vaNeedToBuy, 1) + 1).Value _
= WorksheetFunction.Transpose(vaNeedToBuy)
End Sub

 

[Ken Johnson]

Hi Tom,

Thank you very much for clearing that up for me.
All I had to do was change to Option Base 1.
Now my code's as straightforward as I was originally hoping.

Ken Johnson