Hi All,
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:
Code
- 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