Error, Unable to set formulaArray property of range class
I 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 outputs only the line number. If there is N / A, it will do the same on column "O" which starts as an array and it works fine.
However, 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's happening?
+4
source to share
1 answer
thanks to Dick for pointing me in the right direction and Rory correcting my mistake
http://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html
0
source to share