Using an array formula in a macro, not working

[wnorbury]

Hi I am trying to use an array formula where I need to use cse to make the formula work,
This works when entered manually into a cell however I am trying to use in a macro and keep getting errors.

The formula is
IFERROR(INDEX(MyList,MAX(IF(ISERROR(SEARCH(MyList,T24),-1,1)*(ROW(MyList)-ROW(A$1$)+1))),""No Match"")
However in my macro I have.
Selection.FormulaArray = "=IFERROR(INDEX(MyList,MAX(IF(ISERROR(SEARCH(MyList,T24),-1,1)*(ROW(MyList)-ROW(A$1$)+1))),""No Match"")"

It is looking at a list with a defined name MyList and then in data held in column T. The response is then added to the relevant cell in column H.

There is another problem however as this needs to only work in cells that are blank when column H is filtered for blank cells, where I have T24 in the formula this is a manual entry but is there also a way to have this auto populate from the row it is entered into?

 

[Jonmo1]

Welcome to the board.

I can't even enter the original formula you say works
You have too many arguments in the ISERROR function.
You need one more )

And you have a typo in ROW(A$1$), I assume that's supposed to be $A$1

And you don't use double quotes in the actual cell formula (only in the vba code)

IFERROR(INDEX(MyList,MAX(IF(ISERROR(SEARCH(MyList,T24),-1,1)*(ROW(MyList)-ROW(A$1$)+1))),""No Match"")
Should be
IFERROR(INDEX(MyList,MAX(IF(ISERROR(SEARCH(MyList,T24)),-1,1)*(ROW(MyList)-ROW($A$1)+1))),"No Match")