Looping Vlookup VBA-CodePudding

I have two excel files, where I need to pull the data from one excel file to another with vlookup function through VBA . I placed the below code and tried to make it dynamic

The first piece of code is as under,

Sub Lookup()


' Identifying first and last row

    Const wsName As String = "TB"
    Const hTitle As String = "India"
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim hCell As Range
    Set hCell = ws.Cells.Find(hTitle, , xlFormulas, xlWhole, xlByRows)
    If hCell Is Nothing Then Exit Sub ' header not found
    
    Dim Col As Long: Col = hCell.Column
    Dim fRow As Long: fRow = hCell.Row
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    
    Debug.Print (fRow)
    Debug.Print (lRow)

The above code helps me find "India" in column A, and its First row and last row are identified. As it will be range for me to place vlookup in the next step.

To place vlookup from a different excel file, I used the below code

'Placing vlookup
  
    Dim rw As Long, x As Range
    Dim extwbk As Workbook, twb As Workbook

    Set twb = ThisWorkbook
    Set extwbk = Workbooks.Open("D:\TB_1.xlsx")
        Set x = extwbk.Worksheets("SAP TB").Range("B6:I1048576")

    With twb.Sheets("TBs - Macros")
        For rw = fRow To lRow
            .Cells(rw, 10) = Application.Vlookup(.Cells(fRow, 2).Value2, x, 7, 0)
        Next rw

    End With

    extwbk.Close savechanges:=False
End Sub

The above code accurately picks the first Vlookup value and pastes the same to the entire column range, I think I have erred in placing a loop, due to which only the first value is copied and pasted to the entire range. (as shown in the below snapshot)

It can be seen that 155 is correctly picked in formula, however the same is pasted across entire column.

Sample data is shared in the below link. https://drive.google.com/drive/folders/1inrofeT6v9P0ISEcmbswvpxMMCq5TaV0?usp=sharing Help would be much appreciated!!!

CodePudding user response：

You do not need any iteration in that code area. VLookup function is able to return an array if its arguments are ranges with more rows. Please replace this part:

    With twb.Sheets("TBs - Macros")
        For rw = fRow To lRow
            .Cells(rw, 10) = Application.Vlookup(.Cells(fRow, 2).Value2, x, 7, 0)
        Next rw

    End With

with this one:

   Dim wsM As Worksheet, rngVLk As Range, rngB As Range

   Set wsM = Twb.Sheets("TBs - Macros")
   Set rngVLk = wsM.Range("J" & fRow, "J" & lRow)
   Set rngB = wsM.Range("B" & fRow, "B" & lRow)
   rngVLk.value = Application.VLookup(rngB, x, 7, False)

To avoid problems of the type you faced, please write Option Explicit on top of the module. This will not let you running a piece of code if a used variable has not previously declared and will show/select it.

CodePudding user response：

The problem is in the loop:

With twb.Sheets("TBs - Macros")
    For rw = fRow To lRow
        .Cells(rw, 10) = Application.Vlookup(.Cells(fRow, 2).Value2, x, 7, 0)
    Next rw

End With

You are moving the variable "rw" from "fRow" to "lRow" but you are never using rw.

I think your code should be:

With twb.Sheets("TBs - Macros")
    For rw = fRow To lRow
        .Cells(rw, 10) = Application.Vlookup(.Cells(rw, 2).Value2, x, 7, 0)
    Next rw

End With