Evaluate Match function with variables

Question

I would like to use this through VBA -

=MATCH("PlanA",A:A,0)

with EVALUATE.

Sub Test()

Dim SectionStartRow As Integer    
Dim planname As String
planname = "PlanA"

SectionStartRow = [MATCH(planname,A:A,0)] 'Error 2029 /// Type mismatch '13

End Sub

I've already tried:

SectionStartRow = Evaluate("MATCH(planname,A:A,0)") 'Error 2029 /// Type mismatch '13

and

SectionStartRow = Evaluate("MATCH(" & planname & ",A:A,0)")

but nothing seems to work. Please note that planname variable is denied by a long set of functions.

Answer 1

The problem is that planname needs to be wrapped around string quotes. The excel equivalent is =MATCH("PlanA",A:A,0), and the string that is passed to Evaluate should be identical to that:

SectionStartRow = Evaluate("=MATCH( " & Chr(34) & planname & Chr(34) & ",A:A,0)")

works OK (tested with Excel 2010)

Chr(34) stands for the " symbol in ASCII numbering.

Another way to go about it would be to define

planname = Chr(34) & "PlanA" & Chr(34)

and then do SectionStartRow = Evaluate("=MATCH( " & planname & ",A:A,0)")

By the way, I would define SectionStartRow as Long and not Integer, because Integer will throw an error if the matching row is after 32,767.

Note also that the shorthand form of Evaluate (that is, the square brackets []) won't work in this case. It works only when everything inside the brackets is a constant, not a variable.

I hope this helps!

Answer 2

I stumbled upon this useful question and struggled with connecting not only the planname but also resolve it with a different sheet.

I was confused with the double quotation mark and & symbol from the answer by Ioannis. planname = Chr(34) & "PlanA" & Chr(34)

In my example a version of the below line correctly resolved the first parameter. So only one "& variable &" worked for me.

SectionStartRow = Evaluate("=MATCH('Sheet1'!C " & variablename & ",A:A,0)")