Write vlookup with variable in VBA?

[jaik22]

Hi,

I have this variable to open up the file path in C6 in MAIN tab.

Dim TESTWB As Workbook: Dim dpath As String: dpath = Template.Worksheets("MAIN").Range("C6").Value

but I am trying to do lookup with variables, but it not working.

           Range("X13").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-7],&dpath&!C10:C38,29,FALSE)"

Here is what I wrote to make vlookup. Does any sees why this code is not working?
Any advises are welcome!

 

[jaik22]

Re: how to write vlookup with variable in VBA?

is this path below:

C:\Users\Projects\2018\REPORTS\2018_04Report.xlsx

<tbody>
</tbody>

Thank you!

 

[Joe4]

Re: how to write vlookup with variable in VBA?

Note how your good VLOOKUP function does NOT have the whole file path. It only has the file name, and square brackets. So you need to pull that file name out of there and build the structure.
THAT is what you need to build, something like this:

    Dim dfile As String
    dfile = Mid(dpath, InStrRev(dpath, "\") + 1)
    Range("X13").FormulaR1C1 = "=VLOOKUP(Q13,'[" & dfile & "]main_report'!$J:$AL,29,FALSE)"