title: in Excel VBA, how do I substitute local variables in a MATCH function in place of hard coded values?
body: <p>I've written some VBA code that cycles through the rows of an open Excel datasheet doing an <code>Evaluate(Index(Match))</code> statement, that I found online, that searches for 2 values (LastName &amp; LabCat) in a second Excel Workbook. The result (FirstName) is returned from the second Excel Workbook and stored in a local variable. I successfully return the value I'm looking for with this statement:</p>
  
  <pre><code>strFirstName = Evaluate("INDEX('[MyMatrix.xlsm]MySheet'!$C$2:$C$1000,MATCH(""Cain""&amp;""ABO1"",'[MyMatrix.xlsm]MySheet'!$B$2:$B$1000&amp;'[MyMatrix.xlsm]MySheet'!$H$2:$H$1000,0))")
  </code></pre>
  
  <p>But what I haven't been able to solve is how to substitute local variables for the hard coded values, such as this statement (where I substitute the string "cain" with the variable strLastName and I substitute the string "ABO1" with the variable strLabCat):</p>
  
  <pre><code>strFirstName = Evaluate("INDEX('[MyMatrix.xlsm]MySheet'!$C$2:$C$1000,MATCH(""strLastName""&amp;""strLabCat"",'[MyMatrix.xlsm]MySheet'!$B$2:$B$1000&amp;'[MyMatrix.xlsm]MySheet'!$H$2:$H$1000,0))")
  </code></pre>
  
  <p>This statement returns "Error 2042" into strFirstName.</p>