PDA

View Full Version : Vlookup formula entry



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.

Bob Phillips
02-09-2009, 03:22 PM
Dim x, y, d, e

x = 1: y = 1 'A1
d = 1 'A
e = 5 'E
ActiveCell.FormulaR1C1 = "=VLOOKUP(R" & x & "C" & y & ",'Sheet1'!C" & d & ":C" & e & ",5,FALSE)"

MaximS
02-09-2009, 03:36 PM
why not to try:


ActiveCell.Formula = "=VLOOKUP($A1, Sheet1!'$A$1:$E$" & LastRow & ", 5, FALSE)"

crugg
02-09-2009, 08:26 PM
XLD - thanks, that works well except that the cell values get entered with the $ symbols which makes it impossible to autofill properly. Any thoughts on how to enter without $?

Thanks

Bob Phillips
02-10-2009, 01:40 AM
Dim x, y, d, e

x = 1: y = 1 'A1
d = 1 'A
e = 5 'E
With ActiveCell

.FormulaR1C1 = "=VLOOKUP(R[" & -.Row + x & "]C[" & -.Column + y & "],'Sheet1'!C" & d & ":C" & e & ",5,FALSE)"
End With

crugg
02-17-2009, 08:15 AM
xld -

Thanks! Works great ...