CODEWITHSUNDEEP
excelvba
JBKrarup
JBKrarup

Reputation: 5

Excel vba vlookup range in multiple workbooks

I'm using the following code to do a vlookup into another Workbook using VBA. It's working without any issues, but now I would like it not only to look at a single cell, but loop through a range. Let's say T15:T200.

Anyone who can help modify the code to do this?

Sub VlookMultipleWorkbooksBACKUP()

    Dim lookFor As Range
    Dim srchRange As Range

    Dim book1 As Workbook
    Dim book2 As Workbook

    Dim book2Name As String
    book2Name = "testbog.xlsx"

    Dim book2NamePath As String
    book2NamePath = ThisWorkbook.Path & "\" & book2Name

    Set book1 = ThisWorkbook

    If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
    Set book2 = Workbooks(book2Name)


    Set lookFor = book1.Sheets(1).Cells(15, 20)   ' value to find
    Set srchRange = book2.Sheets(1).Range("A:B")    'source

    lookFor.Offset(0, 20).Value = Application.Vlookup(lookFor, srchRange, 2, False)

End Sub

Thanks!

Upvotes: 0

Views: 321

Answers (1)

YowE3K
YowE3K

Reputation: 23974

Just include a loop in the last part of your code, and use the loop counter variable instead of 15:

'...
    Set book2 = Workbooks(book2Name)
    Set srchRange = book2.Sheets(1).Range("A:B")    'source
    Dim r As Long
    For r = 15 To 200
        Set lookFor = book1.Sheets(1).Cells(r, 20)   ' value to find

        lookFor.Offset(0, 20).Value = Application.Vlookup(lookFor, srchRange, 2, False)
    Next
End Sub

Upvotes: 2

Related Questions