Array Formula not applying to each cell in column

[TiredAnalyst]

Hi,

First step into the world of VBA code. Getting there, although stuck on this.

I have an Array Formula that is not applying the way I need it to. Code is this:

Range("P2:P" & Cells(Rows.Count, "C").End(xlUp).Row).FormulaArray = "=IFNA(INDEX(Sheet2!$C$2:$C$43,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$B$2:$B$43,D2)),0)),Apples)"

The formula runs down the column of around 100k lines but keeps checking against the cell in D2. I need it to check against the cell in column D relevant to the row. i.e. check D100 in row 100. The formula works as when I type it myself and make it an array formula I get the result I need. Thinking this is just my ignorance of VBA showing. Any advice as to what I'm doing wrong is appreciated.

Thanks

 

[jasonb75]

Not something that I've had cause to do but I believe that you need to enter the formula into a single cell, then fill down.

Range("P2").FormulaArray = "=IFNA(INDEX(Sheet2!$C$2:$C$43,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$B$2:$B$43,D2)),0)),Apples)"
Range("P2:P" & Cells(Rows.Count, "C").End(xlUp).Row).Filldown

 

[TiredAnalyst]

Not something that I've had cause to do but I believe that you need to enter the formula into a single cell, then fill down.

Range("P2").FormulaArray = "=IFNA(INDEX(Sheet2!$C$2:$C$43,MATCH(TRUE,ISNUMBER(SEARCH(Sheet2!$B$2:$B$43,D2)),0)),Apples)"
Range("P2:P" & Cells(Rows.Count, "C").End(xlUp).Row).Filldown

So simple!

Thanks very much