Resize and Transpose question.
 

Range("A2").Resize(22,4) represents the range A2:D23.
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 B3:D23 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

Resize and Transpose question.
 

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

--
Regards,
Tom Ogilvy


"Ken Johnson" wrote:

Range("A2").Resize(22,4) represents the range A2:D23.
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 B3:D23 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

Resize and Transpose question.
 

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