I'm really trying to get my head around using a VBA scripting dictionary to do quicker vlookups.
I'm using a simple test sheet with a small range to get my head around how this works.
I am able to load values from a range "B1:B32" into the dictionary, and then retrieve them.
I'm just stuck at how to compare this against another range, if the values match, then return the value. (ie perform like a regular vlookup)
My code so far:
Sub testvlookup() ' Get the range of values Dim rg As Range Dim rg2 As Range Dim lrow As Long Dim sh As Worksheet Set sh = ThisWorkbook.Sheets("Report") Set rg = sh.Range("B1:B32") ' first range we will load in our dictionary Set rg2 = sh.Range("E1:E32") ' second range we are checking against "rg" ' Create the dictionary Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") ' Fill the dictionary Dim cell As Range For Each cell In rg dict(cell.Value) = cell.Value Next ' Perform the Lookups2 lrow = 1 For Each cell In rg Cells(lrow, 7) = dict(cell.Value) lrow = lrow + 1 Next