VBA - VLookup in another sheet with fixed table_array

New Contributor

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:


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 Replies


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


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

@Hans Vogelaar thanks. Awesome solution. Works perfectly.