[VBA] Multi-criteria Index-Match in VBA with Variables oh my!

[RockandGrohl]

Cells(ActiveCell.Row, "I").FormulaArray = "=INDEX('[Advert Creation Master 2020.xlsm]Advert Rows'!C43,MATCH(1,(" & papnam & "='[Advert Creation Master 2020.xlsm]Advert Rows'!C1)*(" & tpl & "='[Advert Creation Master 2020.xlsm]Advert Rows'!C3),0))"

Have a gander at that. Multi Criteria Index Match follows this structure:

{=Index([Results],match(1,([Criteria1]=[Criteria1Range])*([Criteria2]=[Criteria2Range]),0))}

So the Criteria 1 and Criteria 2 I need to change. I have them loaded as "papnam" and "tpl" - these variables are loaded in as "Wolverhampton Chronicle" and "WOL_18x8_3UK"

This gives me this:

=INDEX('[Advert Creation Master 2020.xlsm]Advert Rows'!C43,MATCH(1,(Wolverhampton Chronicle='[Advert Creation Master 2020.xlsm]Advert Rows'!C1)*(WOL_18x8_3UK='[Advert Creation Master 2020.xlsm]Advert Rows'!C3),0))

So nearly there, but I do need to get a "" surrounding the criteria. So I do this:

    Cells(ActiveCell.Row, "I").FormulaArray = "=INDEX('[Advert Creation Master 2020.xlsm]Advert Rows'!C43,MATCH(1,(" & " & papnam & " & "='[Advert Creation Master 2020.xlsm]Advert Rows'!C1)*(" & "& tpl & " & "='[Advert Creation Master 2020.xlsm]Advert Rows'!C3),0))"

And I get "unable to set the FormulaArray property of the range class"


Any ideas? Thanks.

 

[RockandGrohl]

Any ideas? Thanks.

Eureka!

Cells(ActiveCell.Row, "I").FormulaArray = "=INDEX('[Advert Creation Master 2020.xlsm]Advert Rows'!AQ:AQ,MATCH(1,(" & Chr(34) & papnam & Chr(34) & "='[Advert Creation Master 2020.xlsm]Advert Rows'!A:A)*(" & Chr(34) & tpl & Chr(34) & "='[Advert Creation Master 2020.xlsm]Advert Rows'!C:C),0))"

I can "trick" VBA by using chr(34) which is the code for a quote. Take that. Also I noticed that R1C1 style formulas don't work for Array, so I've changed this to the Excel-style formula expression.