Variable file name in Vlookup

    I have a Vlookup in my VBA code that uses a variable for the file name. The syntax appears to be correct yet when I run the code I'm getting single quotes around the range parameters.


    Code
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,'[" & sFilename2 & "]Sheet1'!B2:T5000,11,0)"


    Result is: =VLOOKUP($B2,'[US Dealer Active 2016 0531.xlsx]Sheet1'!'B2':'T5000',11,0)


    The single quotes around B2 and T5000 cause the result to be #NAME?. How do I change the code so I don't get the single quotes?

    Re: Variable file name in Vlookup


    I changed the code to use R1C1 instead of A1 and the code works fine. Should have tried that first before posting the question.

    Re: Variable file name in Vlookup


    Something else that should work, if you wanted to stick with A1 notation, would be to assign the formula to a string variable, then assign the variable as the cell formula. I've used that trick before when I haven't been able to figure out why a formula doesn't apply properly... YMMV.

    Re: Variable file name in Vlookup


    I will give that a try - The R1C1 works fine but it is a little harder to read the A1.


    Thank you for your suggestion

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login