index/match with multiple criteria

[Chris1505]

Range("N8").FormulaArray = "=IF(OR(ISBLANK(INDEX(DoneLast!$O$8:$O$" & OldCnt & ",MATCH(1,(A8=DoneLast!$A$8:$A$" & OldCnt & ")*(D8=DoneLast!$D$8:$D$" & OldCnt & "),0))),ISNA(INDEX(DoneLast!$O$8:$O$" & OldCnt & ",MATCH(1,(A8=DoneLast!$A$8:$A$" & OldCnt & ")*(D8=DoneLast!$D$8:$D$" & OldCnt & "),0)))),""No"",INDEX(DoneLast!$O$8:$O$" & OldCnt & ",MATCH(1,(A8=DoneLast!$A$8:$A$" & OldCnt & ")*(D8=DoneLast!$D$8:$D$" & OldCnt & "),0)))"

=IF(OR(ISBLANK(INDEX(DoneLast!$O$8:$O$23,MATCH(1,(A8=DoneLast!$A$8:$A$23)*(D8=DoneLast!$D$8:$D$23),0))),ISNA(INDEX(DoneLast!$O$8:$O$23,MATCH(1,(A8=DoneLast!$A$8:$A$23)*(D8=DoneLast!$D$8:$D$23),0)))),"No",INDEX(DoneLast!$O$8:$O$23,MATCH(1,(A8=DoneLast!$A$8:$A$23)*(D8=DoneLast!$D$8:$D$23),0)))

If I type the formula (as in 2nd part) directly into a cell and press CTRL+SHIFT+ENTER I get the right results, but when I try it in a macro (1st part) I get "Unable to set the FormulaArray property of the range class(OldCnt at this point equals 23.
It's going to be something silly I've done again isn't it?
Help appreciated

 

[WarPigl3t]

NEver put a formula in a thread without supplying a table with sample data and explaining what you want to accomplish. No one can solve it like this.

 

[VoG]

Not silly but you've encountered the 255 character limit for ,FormulaArray.

This may help you http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

 

[Chris1505]

Thanks VoG, I'll have a "play" and see if I can work around it, maybe break it down into 2 formulas in 2 columns and then put e correct result into a 3rd column-not elegant but if it works .....