long array vba issue

phairplay

Active Member
Joined
Nov 2, 2011
Messages
260
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
It looks like the final part should be:
Code:
.Replace """X_X_X)""", theFormulaPart2

This question seems remarkably familiar... ;)
 
Upvote 0
thank you RoryA
:biggrin:
one off honest

I still can't get it to work it won't add in the next section
 
Last edited:
Upvote 0
I may have miscounted the parentheses - try this:
Code:
.Replace """X_X_X)"")", theFormulaPart2
 
Upvote 0
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
 
Upvote 0
Glad to help. :)
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top