Using a variable within an array (ctrl+shift+enter) formula VBA

Question

I'm trying to use variables which are strings within an array formula. I've used variables within regular formulas in VBA before absolutely fine but no matter how i try to split the formula up using string concatenation (& and " symbols) it doesn't seem to take. The formula works fine with the variables hard coded in.

The code is within a loop, matching the 3 variables (pulled from another worksheet in the workbook) over 3 columns in the current worksheet. It will then return this row location (j) so data from cells in row j can be used elsewhere.

The formula when used in a cell works fine referencing other cells for the variables. ie -

{=MATCH(1,(A:A=H10)*(B:B=H11)*(C:C=H12),0)}

I've then tried stuff along the lines of the below snippet, but get run-time error '1004'

Dim Var1 As String
Dim Var2 As String
Dim Var3 As String
Dim j As Long
Dim test As String
Set Aw = Application.WorksheetFunction

test = "(A:A = """ & Var1 & """)"

  j = Evaluate(Aw.Match(1, [1*" & test & "*(B:B = "Hard_Coded_Var2")*(G:G = "Hard_Coded_Var3")], 0))

Or just

  test = Var1 

  j = Evaluate(Aw.Match(1, [1* (A:A = & test &) *(B:B = "Hard_Coded_Var2")*(G:G = "Hard_Coded_Var3")], 0))

The following works when the variables are hard coded in

  Dim j As Long
  Set Aw = Application.WorksheetFunction

  j = Evaluate(Aw.Match(1, [1* (A:A = "Hard_Coded_Var1")*(B:B = "Hard_Coded_Var2")*(G:G = "Hard_Coded_Var3")], 0))

Ideally the end result would theoretically be like so -

  j = Evaluate(Aw.Match(1, [1* (A:A = Var1)*(B:B = Var2)*(G:G = Var3)], 0))

I've been getting runtime error 404 'Application define or object defined error'

Thanks so much!

I get the error even with hard coded variables. Are you sure it works. As far as I know you will need to use the actual formula in quotes not the vba version in evaluate. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2019-08-22T13:43:11

You will need to use a formula string with Evaluate to use an array type formula:

j = Evaluate("MATCH(1,(A:A=""" & Var1 & """)*(B:B=""" & Var2 & """)*(G:G=""" & Var3 & """),0)")

Using a variable within an array (ctrl+shift+enter) formula VBA

1 Answers