Problems with tranposing an array in VBA

Hi,

I'm building an array with info form different other arrays. Since it is not possible to write rows to an array but only columns, i'm using the following trick:

NewColumn = NewColumn + 1
ReDim Preserve MyZACostPriceTable(1 To 10, 1 To NewColumn)
MyZACostPriceTable(1, NewColumn) = MyIndirectCC
MyZACostPriceTable(2, NewColumn) = MyIndirectCC
MyZACostPriceTable(3, NewColumn) = MyIndirectCCName
MyZACostPriceTable(4, NewColumn) = MyIndirectCC
MyZACostPriceTable(5, NewColumn) = MyIndirectCCName
MyZACostPriceTable(6, NewColumn) = MyTargetZA
MyZACostPriceTable(7, NewColumn) = MyTargetZAName
MyZACostPriceTable(8, NewColumn) = MyTargetZAAmount
MyZACostPriceTable(9, NewColumn) = GetTargetZaCosts
MyZACostPriceTable(10, NewColumn) = GetTargetZaCosts / MyTargetZAAmount

This works fine, but when i want to transpose the array at the end of the code:

MyZACostPriceTable = Application.Transpose(MyZACostPriceTable)

it gives me a data type mismatch error :-(. The total amount of columns after the total array has been filled is 120.645. Can this ne too much?

Regards

Hi,

More like: it will always be 10 columns (after transpose) and > 100.000 rows..

Regards..

Solved it, found another function which i slightly modified:

Function TransposeDim(v As Variant) As Variant
' Custom Function to Transpose a 0-based array (v)

    Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
    Dim tempArray() As Variant

    Xupper = UBound(v, 2)
    Xlower = LBound(v, 2)
    Yupper = UBound(v, 1)
    Ylower = LBound(v, 1)

    ReDim tempArray(Xlower To Xupper, Ylower To Yupper)
    For X = Xlower To Xupper
        For Y = Ylower To Yupper
            tempArray(X, Y) = v(Y, X)
        Next Y
    Next X

    TransposeDim = tempArray

End Function

Thanks for all the repleys!