Error, Unable to set the formulaArray property of the range class

Question

I've created the following formula:

=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))

It looks at column "N" and bring through just the number string. If there is an N/A it will then do the same on column "O" which is ran as an array and it works fine.

Yet when I converted it to VBA code, I get the error mentioned in the title .

Range("L2").Select
    Selection.FormulaArray = _
        "=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))),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))"

What it going wrong?

`.FormulaArray` will only accept a 255 character or fewer string. Yours is too long. — Rory, May 07 '15 at 15:25
Can you show some sample data? It may be possible to shorten the formula. — Rory, May 07 '15 at 15:46
You can put place holders in your formula to get around the limitation. As described here http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/ — Dick Kusleika, May 07 '15 at 15:52

score 5 · Answer 1 · answered May 08 '15 at 10:08

5

thank you Dick for pointing me in the right directions and Rory for correcting my error

http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

answered May 08 '15 at 10:08

Phairplay

273
2
4
16

In my case I also needed to append the following to each .Replace line: , LookAt:=xlPart – flo5783 Nov 07 '19 at 15:32

Error, Unable to set the formulaArray property of the range class

1 Answers1

Linked