VBA Lookup using Scripting Dictionary

jvalente · Jun 8th 2016

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

Display More

**S O** · Jun 8th 2016

Re: VBA Lookup using Scripting Dictionary

Use the exists method

Code

For Each cell In rg 




        If dict.Exists(Cells(lrow, 7).Text) Then
            '// do something
        Else 
            '// do something else
        End If




        lrow = lrow + 1 
Next

Display More

jvalente · Jun 8th 2016

Re: VBA Lookup using Scripting Dictionary

Code

For Each cell In rg
     
     
    If dict.Exists(Cells(lrow, 7).Text) Then
         Cells(lrow, 7) = dict(cell.Value)
    Else
         Cells(lrow, 7) = "NA"
    End If
     
     
    lrow = lrow + 1
Next

Display More

I tried this but it all game back as NAs, when it shouldn't be

**S O** · Jun 8th 2016

Re: VBA Lookup using Scripting Dictionary

I think the logic is wrong, try this:

Code

For Each cell In rg 
     
     
    If dict.Exists(cell.Text) Then 
        Cells(lrow, 7) = dict(cell.Text) 
    Else 
        Cells(lrow, 7) = "NA" 
    End If 
     
     
    lrow = lrow + 1 
Next

Display More

jvalente · Jun 8th 2016

Re: VBA Lookup using Scripting Dictionary

Wait shouldn't it be rg2?

Rg is the first range I load in the dictionary, and the second is the one we are searching against.

Either way I'll try both

sktneer · Jun 8th 2016

Re: VBA Lookup using Scripting Dictionary

Try this....

Code

Sub TestVlookupNew()
Dim x, y, z(1 To 32)
Dim i As Long
Dim dict As Object


x = Sheets("Report").Range("B1:B32").Value
y = Sheets("Report").Range("E1:E32").Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
   dict.Item(x(i, 1)) = x(i, 1)
Next i


For i = 1 To UBound(y, 1)
   If dict.exists(y(i, 1)) Then
      z(i) = y(i, 1)
   Else
      z(i) = "NA"
   End If
Next i
Range("G1:G32").Value = Application.Transpose(z)
End Sub

Display More

**S O** · Jun 8th 2016

Re: VBA Lookup using Scripting Dictionary

Quote

Wait shouldn't it be rg2?

You tell me, I just copied your code from the original post...

jvalente · Jun 10th 2016

Re: VBA Lookup using Scripting Dictionary

Hi all,

I found some code Jindon posted online and altered it and it worked.

Thanks sktneer, your code worked as well!

The code is as follows for those new to this, like me. I put commentary in it to make it less esoteric, for the uninitiated:

Code

' Vlookup Using Scripting Dictionary
    
a = sh.Range("a1").CurrentRegion.Resize(, 23).Value 'load our values from Upload template - Settled
                                                    '(resize 23 means capture column 23 of data)


' Looping to store values in dictionary
        
For i = 2 To lastrow
    dict.Item(a(i, 4)) = a(i, 4) 'Change these column values to return a different value (we want 4th column reference)
Next


' Looping to compare to the other sheet
        
a = sh2.Range("a1").CurrentRegion.Resize(, 29).Value ' The result will be populated on column 29
              
a(1, 29) = "Converted Setts2" ' The title for our result in row 1, column 29


'The loop that will look at our second sheet
        
For i = vrow To lastrow2
    
    If dict.exists(a(i, 5)) Then
    
    a(i, 29) = dict.Item(a(i, 5))
    
    Else: a(i, 29) = "Not Converted"
            
    End If ' if we find column 5 reference, then return on column 29 [a(i,29)], the value of a(i,5) against what we loaded above in the dictionary


Next
    
sh2.Range("a1").CurrentRegion.Resize(, 29).Value = a ' input found values into sh2 on column 29


dict.RemoveAll 'clear the dictionary

Display More

sktneer · Jun 10th 2016

Re: VBA Lookup using Scripting Dictionary

You're welcome. Glad your issue is resolved.

VBA Lookup using Scripting Dictionary

Participate now!

Share