TiredAnalyst
New Member
- Joined
- Feb 18, 2021
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
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
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