Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: application transpose large array

  1. #1
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default application transpose large array

    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

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    Anyone have any thoughts?

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,725
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    Code works fine for me, even when I increase the size of the arrays/ranges tenfold.
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    Quote Originally Posted by Norie View Post
    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?

  5. #5
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    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

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,517
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: application transpose large array

    Quote Originally Posted by MetLife View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    yes the code is the exact one posted

  8. #8
    Board Regular
    Join Date
    Nov 2016
    Posts
    246
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    Hallo,

    too slow, Rick was faster.

    Proposal:

    Code:
    dim surv(1 to 100000, 0 to 1)
    Then Range("A1").resize(ubound(surv),ubound(surv,2)) = surv

    will work.

    regards
    Last edited by Fennek; Feb 21st, 2018 at 04:30 PM.

  9. #9
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    Quote Originally Posted by Fennek View Post
    Hallo,

    too slow, Rick was faster.

    Proposal:

    Code:
    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?

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Posts
    208
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: application transpose large array

    Quote Originally Posted by Rick Rothstein View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •