Can anyone offer any help with the following line of code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R[-60]C[-5]:R[454]C[-4],2,0)"
What I'm asking for it to do is a vlookup on the cell one to the left (=vlookup(RC[-1])
Move to sheet 2 and do a vlookup of the range A1:B454 (Sheet2!R[-60]C[-5]:R[454]C[-4]) and give me the value of column 2 (2,0)
The problem I'm having, because I recorded this as relative references, is it's moving the active cell -5 columns from the activecell column on sheet1 (which is fine, that column is fixed) but the code R[-60) is the difference between the activecell row on sheet1 and A1 on sheet two, and this number increases every time I run the code.
What I think I need is code to replace "Sheet2!R[-60]C[-5]:R[454]C[-4]" with VBA equivalent of "A1:B454". If that makes sense.
Thank you in anticipation.
Tom
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R[-60]C[-5]:R[454]C[-4],2,0)"
What I'm asking for it to do is a vlookup on the cell one to the left (=vlookup(RC[-1])
Move to sheet 2 and do a vlookup of the range A1:B454 (Sheet2!R[-60]C[-5]:R[454]C[-4]) and give me the value of column 2 (2,0)
The problem I'm having, because I recorded this as relative references, is it's moving the active cell -5 columns from the activecell column on sheet1 (which is fine, that column is fixed) but the code R[-60) is the difference between the activecell row on sheet1 and A1 on sheet two, and this number increases every time I run the code.
What I think I need is code to replace "Sheet2!R[-60]C[-5]:R[454]C[-4]" with VBA equivalent of "A1:B454". If that makes sense.
Thank you in anticipation.
Tom
