title: VBA: Using VLOOKUP formula with a dynamic lookup_value
body: <p>I am trying to embed a VLOOKUP formula into my last column after performing formatting on a sheet. My lookup value is for a column that has the following column header: "Company State".
  This is my current flow: </p>
  
  <ol>
  <li><p>Find the Column number with the Column Header: "Company State": </p>
  
  <p><code>CompanyStateColumnNumber = WorksheetFunction.Match("Company State", ws.Range("1:1"), 0)</code></p></li>
  <li><p>Declare the range for lookup. In this case, it exists in a worksheet/workbook that is not the active workbook/worksheet. Hence, I am referencing it using ( I have already declared TZsrcRange as a Range type): </p>
  
  <p><code>Set TZsrchRange = TemplateWS.Range("A:C")</code></p></li>
  <li><p>Now, I am trying to embed the vlookup into my last column (I found my last column in the sheet and it is stored in the variable " NewestLCol". I want to embed it into the column until the very last row (stored in variable "LastRow") :</p>
  
  <pre><code>For x = 2 To LastRow
      Set dynamic_lookup_value = Cells(x, CompanyStateColumnNumber)
      Cells(x, NewestLCol).Formula = " =VLOOKUP(" &amp; _
             dynamic_lookup_value.Address(0, 0) &amp; _
           ", Templatews.Range(A:C).Address(0,0),3, FALSE)" 
  Next x
  </code></pre></li>
  <li><p>Now, when I run it, it doesn't embed the formula and doesn't give me a value. What could my issue be? </p></li>
  </ol>