Can I load a 2-column table into a VBA Dictionary with a single instruction?

[JenniferMurphy]

Suppose I have a table like this one in a sheet.

Weighted Ratings.xlsm
	C	D
7	Product	Part#
8	A	K42-X3
9	B	M21-44
10	C	DL402
11	D	MD3-902
12	E	GT35
MrExcel

Can I load it into a VBA Dictionary or Collection object so that I can search on the key (Product) and get back the value (Part#)?

Or do I need to do it in a loop one row at a time?

Sub DictTest()

Const rnTable As String = "Table16"
Dim NumRows As Long
NumRows = Range(rnTable).Rows.Count
Debug.Print NumRows & " rows"

Dim DictTest As New Dictionary
Dim i As Long
For i = 1 To NumRows
  DictTest.Add Range(rnTable).Cells(i, 1).Value, Range(rnTable).Cells(i, 2).Value
Next i

For i = 1 To NumRows
  Debug.Print "Part# for product " & Range(rnTable).Cells(i, 1).Value _
        & " is " & Range(rnTable).Cells(i, 2).Value
Next i

End Sub

Thanks

 

[shinigamilight]

The values you are outputting as your “proof” are not the actual keys.

Then what are they? kryptonite. lmao

 

[RoryA]

Once again, those are the values of the keys, which are the range objects themselves. Hence:

    Dim dic As New Dictionary
    Dim K As Integer
    
    For K = 1 To 3
        dic(Range("A" & K)) = 1
    Next K
    With dic.Keys()(0)
        MsgBox "Keys are ranges:" & vbLf & _
        "Value: " & .Value & vbLf & _
        "Address: " & .Address & vbLf & _
        "Font name: " & .Font.Name
    End With