long array vba issue

[phairplay]

Hi Guys

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

=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

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?

 

[RoryA]

It looks like the final part should be:

.Replace """X_X_X)""", theFormulaPart2

This question seems remarkably familiar...

 

[phairplay]

thank you RoryA

one off honest

I still can't get it to work it won't add in the next section

 

[RoryA]

I may have miscounted the parentheses - try this:

.Replace """X_X_X)"")", theFormulaPart2

 

[phairplay]

Hi,

it's still on adding the next section in.

sorry to be a pest

 

[RoryA]

Unless you are actually working in R1C1 reference style in Excel, try this:

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

 

[phairplay]

you are a life saver

many thanks

 

[RoryA]

Glad to help.