Resize and Transpose question.

[Ken Johnson]

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

[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

--
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
>
>

[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