VBA VLookUp through datasets and place matched word in selected cells

Question

I need to create a VBA VlookUp that will go through Col A to H in a Sheet2 tab(the table_array), match it with column V(look_up value) in Sheet 1 and put the matching results(which is the column index and its in column 7) in cell AF. I was able to test this for a very small range of values, however it doesnt work for my whole datasets. Column V has over 15000 cells! Below is my code so far but it returns an error. Please help

Sub ADDCLM()
Dim table_Row as Long
Dim table_Clm as Long
Dim LastRow as Long 

LastRow = .Cells(.Rows.Count, "V").End(xlUp).Row
Table1 = Sheet1.Range("V2:V" & LastRow)
Table2 = Sheet2.Range("A:H") 

New_Row = Sheet1.Range("AF2").Row
New_Clm = Sheet1.Range("AF2").Column

For Each c1 in Table1
    Sheet1.Cells(New_Row, New_Clm) = Application.WorksheetFunction.VLookup(c1, Table2, 7, False)
    New_Row = New_Row + 1
Next c1
End Sub

In Excel, this is how the formula looks like: e.g for Cell AF2 =VLOOKUP(V2;Sheet2!A:H;7;FALSE)

I keep getting runtime error 424 on the line table2 = ...

Restrict Table1 to just the range with data, not the entire column. See this question for how to find the last cell. — BigBen
@BigBen I added and removed the lastrow feature but I'm still getting errors unfortunately — Abdlfatah
Please - edit your question with what you tried, the error message, and a note of which line throws the error, thanks! — BigBen

Abdlfatah Abdlfatah · Accepted Answer · 2019-11-06T08:53:16

So, I was able to find a solution to my problem. I renamed my Sheet2 to reflect the sheet name then made some changes to the code with different sources I saw online.

Sub ADDCLM()

    Dim table2 As Range
    Set table2 = Sheets("RD data").Range("A:H")
    Dim lastRow As Long
    With Sheet1
        lastRow = .Cells(.Rows.Count, "V").End(xlUp).Row
        With .Range("AF2:AF" & lastRow)
            .FormulaR1C1 = "=VLOOKUP(RC22," & table2.Address(, , xlR1C1, True) & ", 7, False)"
            .Value = .Value
        End With
    End With

End Sub

VBA VLookUp through datasets and place matched word in selected cells

1 Answers