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.

    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!