VBA - VLookup in another sheet with fixed table_array

[work_in_progress]

Hi guys,

I do have a rather simple problem that probably just causes me trouble do to syntax errors. 

I want to VLOOKUP a cell in a row in a fixed table_array in another sheet. Later, my script autofills this cell onto a variable amount of cells beneath.

So here is the code:

Range("W2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7], old!O1:P100000 ,2,FALSE)"
Selection.AutoFill Destination:=Range("W2:W" & Range("E" & Rows.Count).End(xlUp).Row)

I would expect it to look for each value in the fixed array of old! O:1 to P100000,

In Excel the cell results says: #NAME?

The cell content is: 

=VLOOKUP(P6; old!'O1':'P100000';2;FALSE)

So what he does is that he puts '' in there. 

What I want him to do is to write:

=VLOOKUP(P6; old!O1:P100000;2;FALSE)

What I am doing wrong? 

[#2]

@work_in_progress 

You try to assign an R1C1 formula but P1:P100000 is A1 notation. Use

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],old!R1C15:R100000C16,2,FALSE)"

[#3]

@Hans Vogelaar thanks. Awesome solution. Works perfectly.