Transpose data from an array into a range

[murad5]

Hi,

I'm trying to get data from a variant array (7 x ~8000) into an excel range. When I try to use the following code:
.Range("A2&quot.Resize(UBound(arrSchedule, 2), 7).Value = _WorksheetFunction.Transpose(arrSchedule)
I get the error message "Type Mismatch". It runs ok if I don't try to transpose the array, but then there are too many entries in the array for the number of columns in Excel. Because the array will be a different size every time I run it, I can't have the 8,000 odd dimension as the vertical dimension (or can I?)

Any ideas where I'm going wrong?

Thanks...

 

[SkipVought]

Hi,

Try this -- withou the underscore...

    ActiveSheet.Range("A2").Resize(UBound(arrSchedule, 2), UBound(arrSchedule, 1)).Value = WorksheetFunction.Transpose(arrSchedule)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[murad5]

Still getting the Type Mismatch, I'm afraid. What I've done is set up another array dimensioned as the transpose of arrSchedule, looped through each entry in arrSchedule to put it in this new array and then put the data from this new array into the range. It's not very elegant, but it seems to work quite quickly.

Thanks!

 

[SBendBuckeye]

Hello murad5,

Does something like the following work for you:

Dim lngCol As Long
Dim lngEndRow As Long

lngCol = Ubound(YourArray, 1) ' + 1 if zero based
lngEndRow = Ubound(YourArray, 2) ' + 1 if zero based
Range(Cells(1, lngCol), Cells(lngEndRow, lngCol)) = _
YourArray

I think I saw something somewhere in Help once that said you could do something like the following:

varArray = Range("A1:C3&quot
Range("A1:C3&quot = varArray

I just modified it to use Cells since you have a variable sized array. I hope this didn't turn out to be a rabbit trail for you. Good Luck!


Have a great day!

j2consulting@yahoo.com