0

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!

Community
  • 1
  • 1
Ale
  • 645
  • 4
  • 16
  • 38
  • 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 Mar 11 '14 at 11:42
  • Thanks! I will try to test it. :) about Sub name, ahhah, no, it's just an example – Ale Mar 11 '14 at 11:49

1 Answers1

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)"
AKS
  • 18,983
  • 3
  • 43
  • 54
  • 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) ? – Ale Mar 11 '14 at 13:44