VBA VlookUp for large datasets

[Fatman003]

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 AD. 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


Table1 = Sheet1.Range("V:V")
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)```

 

[Fluff]

How about

Sub Fatman()
    Dim Ary As Variant, Nary As Variant
    Dim i As Long
    Dim Cl As Range
    
    Ary = Sheet1.Range("A1").CurrentRegion.Value2
    With CreateObject("scripting.dictionary")
        For i = 2 To UBound(Ary)
            .Item(Ary(i, 1)) = Ary(i, 7)
        Next i
        Ary = Sheet1.Range("V2", Sheet1.Range("V" & Rows.Count).End(xlUp)).Value2
        ReDim Nary(1 To UBound(Ary), 1 To 1)
        For i = 1 To UBound(Ary)
            If .exists(Ary(i, 1)) Then Nary(i, 1) = .Item(Ary(i, 1))
        Next i
        Sheet1.Range("AD2").Resize(UBound(Nary)).Value = Nary
    End With
End Sub

 

[Domenic]

Here's another way...

Sub test()

    Dim table2 As Range
    Set table2 = Sheet2.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

Notice that this avoids looping.

Hope this helps!

 

[Fatman003]

@Domenic, I get an "object required" error on the Set table2....

 

[Fatman003]

There is no change.. Also, I noticed the second sheet wasnt mentioned in your code

 

[DanteAmor]

Try another aproch

Sub VlookUp_DataSet_2()
  Dim a() As Variant, b() As Variant, t() As Variant, i As Long, j As Long
  a = Sheet1.Range("V1:V" & Sheet1.Range("V" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a))
  t = Sheet2.Range("A1:H" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row).Value
  For i = 1 To UBound(a)
    For j = 1 To UBound(t)
      If a(i, 1) = t(j, 1) Then
        b(i) = t(j, 7)
        Exit For
      End If
    Next
  Next
  Sheet1.Range("AF1").Resize(UBound(a)).Value = Application.Transpose(b())
End Sub

 

[Fluff]

Oops typo, after testing, it should be

Ary = Sheet[COLOR=#ff0000]2[/COLOR].Range("A1").CurrentRegion.Value2

 

[Domenic]

@Domenic, I get an "object required" error on the Set table2....

Your original code refers to Sheet2, can you please confirm that this is in fact the code name for your sheet?

Also, I noticed the second sheet wasnt mentioned in your code

It's referred to using the "With/End" With statement...

    [COLOR=#ff0000]With Sheet1[/COLOR][COLOR=#574123]
[/COLOR]        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
     [COLOR=#ff0000]End With[/COLOR]

 

[Fluff]

@Domenic
I suspect that this

Also, I noticed the second sheet wasnt mentioned in your code

was aimed at me rather than you.

 

[Domenic]

@Domenic
I suspect that this was aimed at me rather than you.

Oh, okay, thanks Fluff...