excelvbaevaluateexcel-match

Evaluate Match function with variables

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.

Solution

  • 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!