Trying to fill VLOOKUP in VBA, but lookp only works for 2 cells

Question

I'm trying to write a VBA code thta helps me Vlookup value from another location on C Drive, but apparently only the first two cells work.

Could you help me correct this code? Really new to VBA and just trying my waters with the Do While Looops.

Sub Copy3()

    Dim lookfor As Range
    Dim table_array As Range
    Dim table_array_col As Integer
    Dim lookFor_col As Integer
    Dim Wbk As Workbook
    Dim Rows As Integer

    Rows = 2
    Do While Rows < 60

    Set lookfor = Cells(Rows, 1)

    Set Wbk = Workbooks.Open("C:\Users\XXX.xlsx")
    Set table_array = Wbk.Sheets("Sheet1").Range("B2:H60")
    table_array_col = 5

    lookFor_col = ThisWorkbook.Sheets("Sheet1").UsedRange.Columns.Count - 2
    ThisWorkbook.Sheets("Sheet1").Cells(Rows, lookFor_col).Formula = Application.VLookup(lookfor.Value, table_array, table_array_col, 0)

    Rows = Rows + 1
    Loop
End Sub

Are you trying to write a formula to the cell, or a value? Then note that `Set Wbk = Workbooks.Open("C:\Users\XXX.xlsx")` (and other lines) should be *outside* the loop. — BigBen, Jun 02 '21 at 14:17
Also consider consistent casing, and avoid snake_case. Then change all those `Integer` to `Long`.... there's no benefit to using `Integer` here, see [this](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, Jun 02 '21 at 14:59

Toddleson · Answer 1 · 2021-06-02T14:56:08.923

I think I found the error. Set lookfor = Cells(Rows, 1) was missing a qualifier, and was likely not referencing the correct workbook. Workbooks.Open changes the Active Workbook. Unqualified ranges are prone to cause errors when the Active Workbook changes during execution.

I corrected that by adding a reference to ThisWorkbook.Sheets("Sheet1") and also cleaned up some other minor problems that I noticed. Notably, Rows is one of the Range Objects built into Excel VBA. Its best to avoid using defined object names as your variable names when you can.

Sub Copy3()
    
    Const table_array_col As Integer = 5
    
    Dim lookfor As Range
    Dim Row As Integer
    
    Dim lookFor_col As Integer
    lookFor_col = Sh.UsedRange.Columns.Count - 2
    
    Dim Wbk As Workbook
    Set Wbk = Workbooks.Open("C:\Users\XXX.xlsx")
    
    Dim table_array As Range
    Set table_array = Wbk.Sheets("Sheet1").Range("B2:H60")
    
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Sheets("Sheet1")
    
    For Row = 2 To 59

        Set lookfor = Sh.Cells(Row, 1)
        
        Sh.Cells(Row, lookFor_col).Value = Application.VLookup(lookfor.Value, table_array, table_array_col, 0)
    
    Next Row
End Sub

Trying to fill VLOOKUP in VBA, but lookp only works for 2 cells

1 Answers1