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!

 

[Special-K99]

Re: how to write vlookup with variable in VBA?

You cant put a variable in the middle of a string and hope that Excel can see it as a variable.

Try

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

 

[jaik22]

Re: how to write vlookup with variable in VBA?

You cant put a variable in the middle of a string and hope that Excel can see it as a variable.

Try

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

I am getting "Object doesn't support this property or method" error.
How can I resolve this issue?

Thank you so much for your supprot

 

[Special-K99]

Re: how to write vlookup with variable in VBA?

Not a VBA expert.

Can you assign the dpath result to an integer variable and put that in?
e.g.

Dim dpath2 as integer
dpath2=dpath

Insert dpath2 into the VLOOKUP instead of dpath

 

[jaik22]

Re: how to write vlookup with variable in VBA?

Seems like its not allowing me to setup integer variable like that..

 

[Special-K99]

Re: how to write vlookup with variable in VBA?

Sorry, just noticed dpath is a string
Dim dpath2 to a string

 

[jaik22]

Re: how to write vlookup with variable in VBA?

Hi,

I was able to setup the variable, but I am getting the different error. "Application-defined or object-defined error"
Do see any flaws from my formula?

Thank you!

 

[Joe4]

Re: how to write vlookup with variable in VBA?

What is your value of "dpath"?
What does a good VLOOKUP formula on your sheet looking up that range look like?
That is what you need to build, exactly.

Also note:
Your current structure is inefficient and will actually slow your code down. You do not need to select a range before applying a formula or value to it.
Just structure it like this one line:

   Range("X13").FormulaR1C1 = ...

 

[jaik22]

Re: how to write vlookup with variable in VBA?

the value of dpath is

C:\Users\Projects\2018\REPORTS\2018_04Report.xlsx and good vlookup looks like

<tbody>
</tbody>

The correct vlookup is this:

=VLOOKUP(Q13,'[2018_04Report.xlsx]main_report'!$J:$AL,29,FALSE)

However workbook name changes depends on what user puts value into C6(dpath).

And thanks for the tip on optimizing the code!

 

[Joe4]

Re: how to write vlookup with variable in VBA?

However workbook names changes depends what value user puts into C6.

So, for this particular example, exactly what would be in cell C6?