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
– Ioannis, Commented Mar 11, 2014 at 11:42
Thanks! I will try to test it. :) about Sub name, ahhah, no, it's just an example — Ale
– Ale, Commented Mar 11, 2014 at 11:49

AKS · Accepted Answer · 2014-03-11 12:05:35Z

0

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)"

answered Mar 11, 2014 at 12:05

AKS

20k3 gold badges47 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ale Over a year ago

May I ask how to write in a correct way the function, if I use: Sal = Application.VLOOKUP(currentsheet.Range("B11").Value, ActiveWorkbook.Sheets("Files").Range("$A$1:$B$" & LastRowA), 1, False) ?

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related