Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: long array vba issue

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    260
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default long array vba issue

    Hi Guys

    I have an issue with an array being to long for my vba

    Code:
    =IFNA(LOOKUP(10^99,--MID(O2,MIN(IF((--ISNUMBER(--MID(O2,ROW($1:$25),1))=0)*ISNUMBER(--MID(O2,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25))),SUMPRODUCT(MID(0&RIGHT(N2,4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(N2,4),ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))
    this formula works great yet I need to use it in a vba

    I found a code that will split it out but when I use it I only see the formula not the result
    Code:
    Sub LongArrayFormula()
         Dim theFormulaPart1 As String
         Dim theFormulaPart2 As String
         theFormulaPart1 = "=IFNA(LOOKUP(10^99,--MID(RC[3],MIN(IF((--ISNUMBER(--MID(RC[3],ROW(R1:R25),1))=0)*ISNUMBER(--MID(RC[3],ROW(R2:R26),1)),ROW(R2:R26))),ROW(R1:R25))),""X_X_X)"")"
                              
         theFormulaPart2 = "SUMPRODUCT(MID(0&RIGHT(RC[2],4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(RC[2],4),ROW(R1:R25),1))* ROW(R1:R25),0),ROW(R1:R25))+1,1)*10^ROW(R1:R25)/10))"
            
         With ActiveSheet.Range("L2")
             .FormulaArray = theFormulaPart1
            .Replace "X_X_X)", theFormulaPart2
     
        End With
         
    End Sub
    can you please tell me what I'm doing wrong?

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,414
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: long array vba issue

    It looks like the final part should be:
    Code:
    .Replace """X_X_X)""", theFormulaPart2
    This question seems remarkably familiar...

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

    Default Re: long array vba issue

    thank you RoryA

    one off honest

    I still can't get it to work it won't add in the next section
    Last edited by phairplay; May 8th, 2015 at 06:15 AM.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,414
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: long array vba issue

    I may have miscounted the parentheses - try this:
    Code:
    .Replace """X_X_X)"")", theFormulaPart2

  5. #5
    Board Regular
    Join Date
    Nov 2011
    Posts
    260
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: long array vba issue

    Hi,

    it's still on adding the next section in.

    sorry to be a pest

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,414
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: long array vba issue

    Unless you are actually working in R1C1 reference style in Excel, try this:
    Code:
    Sub LongArrayFormula()
         Dim theFormulaPart1 As String
         Dim theFormulaPart2 As String
         theFormulaPart1 = "=IFNA(LOOKUP(10^99,--MID(O2,MIN(IF((--ISNUMBER(--MID(O2,ROW($1:$25),1))=0)*ISNUMBER(--MID(O2,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25))),""X_X_X)"")"
                              
         theFormulaPart2 = "SUMPRODUCT(MID(0&RIGHT(N2,4),LARGE(INDEX(ISNUMBER(--MID(RIGHT(N2,4),ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))"
         With ActiveSheet.Range("L2")
             .FormulaArray = theFormulaPart1
            .Replace """X_X_X)"")", theFormulaPart2
     
        End With
         
    End Sub

  7. #7
    Board Regular
    Join Date
    Nov 2011
    Posts
    260
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: long array vba issue

    you are a life saver

    many thanks

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,414
    Post Thanks / Like
    Mentioned
    46 Post(s)
    Tagged
    5 Thread(s)

    Default Re: long array vba issue

    Glad to help.

Some videos you may like

User Tag List

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
  •