Sep 21 2022 06:06 AM
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?
Sep 21 2022 06:24 AM
You try to assign an R1C1 formula but P1:P100000 is A1 notation. Use
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],old!R1C15:R100000C16,2,FALSE)"
Sep 22 2022 04:19 AM
@Hans Vogelaar thanks. Awesome solution. Works perfectly.