VBA Vlookup - Correct Way w/ Loops

[SteveOranjinSteve]

Hello,

I'm a fairly rudimentary VBA user. I'm trying to get something working in a tutorial so I can start doing some vlookups through VBA on my own, but I need an example I can work from. My guess is that in order for a successful table to be populated, we have to write some kind of loop that allows the vlookup to loop through the lookup table and return it (in the "return table".) So far all I've got is the actual VLOOKUP working, I don't know how to institute two different loops @ the same time, so that it'll loop through the one table, and then loop through the other table, return the result, and then go back to the other table and grab the next result. Can someone show me the code that would be required to do this? The link for the sheet is here.

https://1drv.ms/x/s!AuLIQSvmFBoEngRYVzrkMYNwf1tZ

 

[Fluff]

Personally I would not use vlookup in a macro, as it tends to be slow, especially on large amounts of data.
I would use a dictionary, failing that either loop through an array, or use Range.Find
Does it need to be a vlookup?

 

[DanteAmor]

Run this macro on the sheet "return table"

Sub Macro3()
  Dim lr As Long
  lr = Sheet3.Range("A" & Rows.Count).End(xlUp).Row
  With Range("G3:G" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=VLOOKUP(RC[-6],lookup_table!R3C1:R" & lr & "C4,3,0)"
    .Value = .Value
  End With
  With Range("H3:H" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=VLOOKUP(RC[-7],lookup_table!R3C1:R" & lr & "C4,4,0)"
    .Value = .Value
  End With
End Sub

 

[SteveOranjinSteve]

Personally I would not use vlookup in a macro, as it tends to be slow, especially on large amounts of data.
I would use a dictionary, failing that either loop through an array, or use Range.Find
Does it need to be a vlookup?

No, it does not need to be a Vlookup. I am looking to practice using VBA, which is why I am doing it.

Kinda having a hard time finding practical tasks which I can apply myself to in the beginning of learning.

Do you know of any good tutorials?

Steve

 

[Fluff]

In that case, using a dictionary

Sub SteveOranjin()
    Dim Cl As Range
    Dim Dic As Object
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Lookup_table")
        For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
            Dic(Cl.Value) = Cl.Offset(, 2).Resize(, 2).Value
        Next Cl
    End With
    With Sheets("Return table")
        For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
            If Dic.Exists(Cl.Value) Then Cl.Offset(, 6).Resize(, 2).Value = Dic(Cl.Value)
        Next Cl
    End With
End Sub

For more info on dictionaries, have a look here Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery