cell ref in a formula

[miek]

I have the following formula that works
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7], R2C10:R683C11, 2, FALSE)"

but I would like to use dymanic varaibles. However the below does not work
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7], $J$&"varX":$K$ & "varY", 2, FALSE)"

could someone help with the syntax
Thanks

 

[James Snell]

There's a couple of errors there:
- first you're setting FormulaR1C1 but not providing an R1C1 format formula
(you're actually using a mix of R1C1 & A1 content. it would probably work but
it could cause you problems later.
- secondly your string handling is broken. I'd be suprised if the VBA
editor let you use it at all.

Here's the code you meant to write...

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7], R10C" & VarX & ":R113C" & VarY &
", 2, FALSE)"