CODEWITHSUNDEEP
excelvbavlookup
U.Cremona
U.Cremona

Reputation: 65

VBA - VLOOKUP in different workbooks

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

Upvotes: 1

Views: 2003

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

Upvotes: 1

Related Questions