How to define range in VLOOKUP, using different sheets and variables in VBA?

Question

Doing a VLOOKUP function i use 1 column from sheet1 and 2 columns from sheet "Files" inside the same workbook. I have a problem with defining range from sheet called "Files":

Sub VLOOKUP()
Dim Dept_Row, Dept_Clm As Long
Dim LastRowA, LastRowB As Long
Set currentsheet = ActiveWorkbook.Sheets(1)
ctr = 0
LastRowB = currentsheet.Range("B" & Rows.Count).End(xlUp).Row
LastRowA = Sheets("Files").Range("A" & Rows.Count).End(xlUp).Row
Table1 = currentsheet.Range("B11:B" & LastRowB)
Table2 = Sheets("Files").Range("A1:A" & LastRowA)

Dept_Row = currentsheet.Range("F11").Row
Dept_Clm = currentsheet.Range("F11").Column
For Each cl In Table1
currentsheet.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4], Files!R1C1:R & LastRowA & Files!C2, 2, False)"
Dept_Row = Dept_Row + 1
ctr = ctr + 1
Next cl

'Sal = Application.VLOOKUP(currentsheet.Range("B11").Value, ActiveWorkbook.Sheets("Files").Range("$A$1:$B$" & LastRowA), 1, False)

 End Sub

The problem appears at line currentsheet.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4], Files!R1C1:R & LastRowA & Files!C2, 2, False)"

or

Sal = Application.VLOOKUP(currentsheet.Range("B11").Value, ActiveWorkbook.Sheets("Files").Range("$A$1:$B$" & LastRowA), 1, False).

I found similar topics here: Using VBA to enter a vlookup function in a cell using user chosen file and VBA Vloopup using 2 different workbooks

But did not manage to make it work. Maybe someone could help, how to define range in the right way, using different Sheets and LastRow as a variable? Thanks!

I would use a full reference to Rows. When used as Rows it refers to the Activesheet of the Activeworkbook. You might want to try currentsheet.Rows instead, and the same for the other sheet. Also, your Dept_Row and Last_Row variables are actually Variant, not Long.. You might want to declare them explicitly as Long. One more thing: I hope the actual Sub name is not VLOOKUP()? :) — Ioannis
Thanks! I will try to test it. :) about Sub name, ahhah, no, it's just an example — Ale

AKS AKS · Accepted Answer · 2014-03-11T12:05:35

You are using LastRowA inside a string

currentsheet.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4], Files!R1C1:R & LastRowA & Files!C2, 2, False)"

which should have been:

currentsheet.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4], Files!R1C1:R" & LastRowA & "C2, 2, False)"

How to define range in VLOOKUP, using different sheets and variables in VBA?

1 Answers