VBA: Using VLOOKUP formula with a dynamic lookup_value

Question

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:

Find the Column number with the Column Header: "Company State":

CompanyStateColumnNumber = WorksheetFunction.Match("Company State", ws.Range("1:1"), 0)
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):

Set TZsrchRange = TemplateWS.Range("A:C")

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") :

For x = 2 To LastRow
    Set dynamic_lookup_value = Cells(x, CompanyStateColumnNumber)
    Cells(x, NewestLCol).Formula = " =VLOOKUP(" & _
           dynamic_lookup_value.Address(0, 0) & _
         ", Templatews.Range(A:C).Address(0,0),3, FALSE)" 
Next x

Now, when I run it, it doesn't embed the formula and doesn't give me a value. What could my issue be?

Please add your code, so someone can help you. Provide any error that you get. — GMalc
Please consider pasting your code and explaining a bit more generally what the objective might be. — Display name
Also run your code step by step using the F8 key, ensuring your code indeed gets to the Formula assignation. If an error occurs, state its number and exact wording in the question. — Excelosaurus
Your Cells calls need to be qualified with a worksheet object to be sure you're referencing the sheet you expect. — Tim Williams

usmanhaq usmanhaq · Accepted Answer · 2018-11-20T02:56:19

Try replacing your formula assignment with this one

Cells(x, NewestLCol).Formula = "=VLOOKUP(" & _
                                dynamic_lookup_value.Address(0, 0) & _
                                "," & _
                                TZsrchRange.Address(External:=True) & _
                                ",3, FALSE)"

VBA: Using VLOOKUP formula with a dynamic lookup_value

1 Answers