1

I am completely new to VBA and Excel macros in general. I have been trying to do a VLOOKUP with VBA across different workbooks, but after hours of trying I am unable at the moment to solve this problem and your help would be very much appreciated.

Basically the situation is the following: I have two files, "Book 1" and "Book 2".

In "Sheet 1" of "Book 1" I have a list of values, starting from C19 until C1000, that need to be looked up in "Sheet 2" of "Book 2". These same values contained in "Sheet 2" of "Book 2" start from B5, and the searched values start from N5 (always in "Sheet 2" of "Book 2"). The searched values then need to be pasted in "Sheet 1" of "Book 1" starting from cell I19 .

PROBLEM: The code is only doing the Vlookup for the first cell. I think is because "myLookupValue" is defined as String. Any tips on how to make it go through all the cells?

Thanks in advance.

CODE:


Sub VLookup()

On Error Resume Next

 Dim myLookupValue As String
 Dim myFirstColumn As Long
 Dim myLastColumn As Long
 Dim myColumnIndex As Long
 Dim myFirstRow As Long
 Dim myLastRow As Long
 Dim myVLookupResult As String
 Dim myTableArray As Range

 Set wb1 = Workbooks("Book 1.xlsb").Sheets("Sheet 1")
 myLookupValue = Sheets("Sheet 1").Range("C19")
 myFirstColumn = 2
 myLastColumn = 14
 myColumnIndex = 13
 myFirstRow = 5
 myLastRow = 5000
 With Workbooks("Book 2.xlsx").Worksheets("Sheet 2")
 Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
 End With

 myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)
 Sheets("Sheet 1").Range("I19").Value = myVLookupResult

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
U.Cremona
  • 65
  • 10
  • 1
    First of all, remove `On Error Resume NExt`. That command just hide errors but they still occur. Second, about *The code is only doing the Vlookup for the first cell* , you'll need to loop, check VBA loops. *Any tips on how to make it go through all the cells?* Yes, check VBA loops, you'll need them if you are learning VBA macros – Foxfire And Burns And Burns Aug 09 '20 at 00:28

1 Answers1

1

Something like this should guide you. I just added a Loop to your code, you were almost there!

Sub VLookup()

Dim myLookupValue As String
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim myColumnIndex As Long
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myVLookupResult As String
Dim myTableArray As Range

Dim i As Long

myFirstColumn = 2
myLastColumn = 14
myColumnIndex = 13
myFirstRow = 5
myLastRow = 5000

With Workbooks("Book 2.xlsx").Worksheets("Sheet 2")
    Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
End With

Set wb1 = Workbooks("Book 1.xlsb").Sheets("Sheet 1")

For i = 19 To 1000 Step 1 'start at 19 because your range is C19:1000
    myLookupValue = Sheets("Sheet 1").Range("C" & i)
    myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)
    Sheets("Sheet 1").Range("I" & i).Value = myVLookupResult
Next i

End Sub
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • Very kind of you, thank you very much, the code works! I just have a question: since I'm working with dynamic workbooks and sheets (meaning that my lists vary in length - the C1000 was an example, as in one case I can have until C569, and in another case I can have until C343), I would need a "while"- loop, that goes through all the cells, looks up values and pastes then the searched values until an empty cell is found. – U.Cremona Aug 09 '20 at 06:42
  • With the code you gave me (which works perfectly), I would need to instead manually insert the amount of rows. How could I do it @FoxfireAndBurnsAndBurns? Thanks in advance – U.Cremona Aug 09 '20 at 06:45
  • You can get the last non blank row in a specific column and use it instead of 1000. Probably you will need to loop through all workbooks, calculate each time last row, and then loop each vlookup. To find last non blank cell in a column, check https://stackoverflow.com/a/11169920/9199828 – Foxfire And Burns And Burns Aug 09 '20 at 08:12
  • Thanks. I added the part found in the link you sent me (where it says "Find Last Row in a Column": With Sheets("Sheet1") LastRow = .Range("C" & .Rows.Count).End(xlUp).Row End With) and then replaced 1000 (in the "For" loop) with "LastRow". Nevertheless, I receive error 1004 and it doesn't work. What do I do wrong? – U.Cremona Aug 09 '20 at 08:26
  • Google for some examples, store the last row number in a variable and just use it in your loop instead of 1000 – Foxfire And Burns And Burns Aug 09 '20 at 11:40
  • the problem is the "For" formula, because it requires an exact number. I tried to define the variable and use it in the loop instead of 1000, but it doesn't work – U.Cremona Aug 09 '20 at 20:16