crugg
02-09-2009, 02:45 PM
Greetings,
I am trying to write code that enters a vlookup formula in the active cell - like such:
(from Sheet 2)
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C[-5]:C[-1],5,FALSE)"
My question is: How can I write this with variable representing absolute cell addresses rather than with the RC method above which is relative to where the cursor is at the time? In other words, I want to write something like this:
ActiveCell.FormulaR1C1 = "=VLOOKUP(' & x & ',Sheet1!' & y & ':' & z & ',5,FALSE)"
Where x = A1 (or some other cell address), y = A (or other), z = E (or other)
I tried this method and it does enter the formula but it returns an error because (I think) the formula is not entered in RC format.
Thanks for any help.
I am trying to write code that enters a vlookup formula in the active cell - like such:
(from Sheet 2)
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C[-5]:C[-1],5,FALSE)"
My question is: How can I write this with variable representing absolute cell addresses rather than with the RC method above which is relative to where the cursor is at the time? In other words, I want to write something like this:
ActiveCell.FormulaR1C1 = "=VLOOKUP(' & x & ',Sheet1!' & y & ':' & z & ',5,FALSE)"
Where x = A1 (or some other cell address), y = A (or other), z = E (or other)
I tried this method and it does enter the formula but it returns an error because (I think) the formula is not entered in RC format.
Thanks for any help.