Thanks:  0
Likes:  0

# Thread: long array vba issue

1. ## 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. ## 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. ## 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

4. ## Re: long array vba issue

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

5. ## Re: long array vba issue

Hi,

it's still on adding the next section in.

sorry to be a pest

6. ## 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. ## Re: long array vba issue

you are a life saver

many thanks

## User Tag List

#### Posting Permissions

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