Problem retrieving item from Excel VBA Dictionary

[Thermal]

Hi,

Forgive me if this is a simple question. I am a novice and have been battling with a dictionary item retrieval issue for quite a while, spent days searching the internet for an answer and have given up in favour of posting my own question. Any help will be very much appreciated.

My issue is I cannot retrieve the data indexed by a particular key. Example code:

Dim Dict01 As New Dictionary
Sub Macro1()
'
' Macro1 Macro
'
'
Dim Count As Integer
Dim CurCell As Range
Dim Keys As Variant
Dim Iterate As Variant
Dim Test As Variant
Set Dict01 = New Dictionary
'
' Row 1 is where the dictionary keys live.
' Row 2 and onward is where the data referenced by the keys is located.
'
Range("A1").Select
Set CurCell = Range(Selection, Selection.End(xlToRight))
CurCell.Select
'
' Creates dictionary keys
'
For Each Iterate In CurCell
Dict01(Iterate) = ""
Next

Keys = Dict01.Keys
Count = 0
'
' Selects data for loading into dictionary
'
Range("A2").Select

Set CurCell = Range(Selection, Selection.End(xlToRight))
CurCell.Select
For Each Iterate In CurCell
Dict01(Keys(Count)) = Iterate
Count = Count + 1
Next
Test = Keys(0)
MsgBox (Dict01.Item(Keys(0))) ' Works! returns data identified but 1st key
MsgBox Test 'Proves Test contains correct key
MsgBox (Dict01.Item(Test)) ' Does not work! displays empty content

End Sub

I do not understand why the last Msgbox statement returns 'nothing' rather than the data held by Keys(0).

For completeness, the data in the sheet would look like this:

A B C
1 Ford Audi BMW
2 Ka A5 X6

I expect the 3 Msgbox's to return

Ka - Output as expected
Ford - Output as expected
Ka - No output.

Many thanks in advance.

 

[shg]

You might want to read about the properties and methods of a dictionary object in Help (Microsoft Visual Basic Documentation > Visual Basic Language Reference > Objects > Dictionary).

You add items to a dictionary using the Add method. You can't access an item in a dictionary by position except by reading all of the Keys into a variant array, which kind of defeats the purpose of a dictionary.

I think this is part of what you're trying to do:

Dim dic          As Dictionary
 
Sub Macro1()
    Dim r           As Range
    Dim cell        As Range
    Dim avKeys      As Variant
 
    Set dic = New Dictionary
    Set r = Range("A1", Range("A1").End(xlToRight))
 
    For Each cell In r
        If Not dic.exists(cell.Value) Then dic.Add Key:=cell.Value, Item:=cell.Offset(1).Value
    Next cell
    avKeys = dic.Keys
End Sub

 

[ZVI]

Dict01(Iterate) = "" adds the Iterate cell object not its value
For solving replace it by: Dict01(Iterate.Value) = ""

 

[pgc01]

Hi Thermal
Welcome to the board

Since you are having problems retrieving the item, here are some examples at the end of the Sub.

shg, hope you don't mind that I borrowed your code.

Sub Macro1()
    Dim r           As Range
    Dim cell        As Range
    Dim avKeys      As Variant
    Dim dic         As Dictionary
 
    Set dic = New Dictionary
    Set r = Range("A1", Range("A1").End(xlToRight))
 
    For Each cell In r
        If Not dic.Exists(cell.Value) Then dic.Add Key:=cell.Value, Item:=cell.Offset(1).Value
    Next cell
 
    ' 3 ways to display the second item
    avKeys = dic.Keys
 
    MsgBox dic.Item(avKeys(1))
    MsgBox dic(avKeys(1))
 
    MsgBox dic.Items(1)
 
    ' other ways to display the second item, using the range
    MsgBox dic.Item(r.Offset(0, 1).Resize(1, 1).Value)
    MsgBox dic(r.Offset(0, 1).Resize(1, 1).Value)
 
    MsgBox dic(r(2).Value)
 
End Sub

 

[Thermal]

Thank you all for your responses, really useful. Clearly I have some work to do on my understanding of how dictionaries function, I will jump to it.

Kind regards.