application transpose large array

[MetLife]

hi,

i want to take an array and transpose it but am having issues. the problem appears to be the size of the array, does anyone know a fix?




Sub stochastic()
'commutation functions
Dim surv(1 To 100000) As Double
Dim survv(1 To 100) As Double
Dim i As Long, j As Long


For i = 1 To 10000
If i > 99 Then
j = 99
Else
j = i
End If
surv(i) = Rnd
survv(j) = Rnd
Next i
Range("b2:b10000") = Application.Transpose(surv) 'error type mismatch
Range("b2:b100") = Application.Transpose(survv) 'no error

end sub

 

[MetLife]

Anyone have any thoughts?

 

[Norie]

Code works fine for me, even when I increase the size of the arrays/ranges tenfold.

 

[MetLife]

Code works fine for me, even when I increase the size of the arrays/ranges tenfold.

I just reran in a new workbook and still get an error

"type mismatch"

are you running the exact code I have?

 

[MetLife]

for me the sizes work below the following
Dim surv(1 To 65536) As Double

but one larger and it crashes
Dim surv(1 To 65537) As Double

I am using excel 2010 on a 64bit macine, windows 10

 

[Rick Rothstein]

I just reran in a new workbook and still get an error

"type mismatch"

are you running the exact code I have?

Is the code you posted exactly the code you are using? I ask because you dimensioned your array for 100,000 elements but your loop is only filling 10,000 elements and you are only trying to transpose 10,000 values. The Transpose function has a maximum limit (cannot be overridden) of 65535 elements that it can transpose.

 

[MetLife]

yes the code is the exact one posted

 

[Fennek]

Hallo,

too slow, Rick was faster.

Proposal:

dim surv(1 to 100000, 0 to 1)

Then Range("A1").resize(ubound(surv),ubound(surv,2)) = surv

will work.

regards

 

[MetLife]

Hallo,

too slow, Rick was faster.

Proposal:

dim surv(1 to 100000, 0 to 1)

Then Range("A1").resize(ubound(surv),ubound(surv,2)) = surv

will work.

regards

Well this works, so thank you!

Wonder what the issue was, are you able to run the original code?

 

[MetLife]

Is the code you posted exactly the code you are using? I ask because you dimensioned your array for 100,000 elements but your loop is only filling 10,000 elements and you are only trying to transpose 10,000 values. The Transpose function has a maximum limit (cannot be overridden) of 65535 elements that it can transpose.

OK thanks for that information Rick.