Retrieve dictionary value using key

[tiredofit]

My worksheet is as follows:

Key Metric1 Metric2
1     a         aa

I am trying to retrieve a value using the dictionary's key:

Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary
    
    dic.Add Cells(2, 1), Cells(2, 2)
    
    Debug.Print dic.Item(1)

but it does nothing.

What is wrong?

Thanks

 

[wideboydixon]

The Key/Value pairs can be anything you like. In your case, you're adding ranges not values. Try this instead?

Dim dic As Scripting.Dictionary

Set dic = New Scripting.Dictionary

dic.Add Cells(2, 1).Value, Cells(2, 2).Value

Debug.Print dic.Item(1)

WBD

 

[Fluff]

It should be

Debug.Print dic.Items(0)

 

[tiredofit]

Thanks

Further to this, if I want to use the key to retrieve Metric1, I can use this:

dic(cells(2,1).Value)

However, how can I retrieve the value in Metric2?


(Essentially I have assigned ONE Key with TWO values. Does that break the rules for dictionaries)?

 

[tiredofit]

The Key/Value pairs can be anything you like. In your case, you're adding ranges not values. Try this instead?

Dim dic As Scripting.Dictionary

Set dic = New Scripting.Dictionary

dic.Add Cells(2, 1).Value, Cells(2, 2).Value

Debug.Print dic.Item(1)

WBD

Thanks but this returns blank.

 

[wideboydixon]

You can't. Why not add the row as the value in the dictionary:

Dim dic As Scripting.Dictionary

Set dic = New Scripting.Dictionary

dic.Add Cells(2, 1).Value, 2 ' 2 is the row number

Debug.Print Cells(dic.Item(1), 2).Value
Debug.Print Cells(dic.Item(1), 3).Value

Prints "a" and "aa" on my Excel.

WBD

 

[tiredofit]

You can't. Why not add the row as the value in the dictionary:

 Dim dic As Scripting.Dictionary

 Set dic = New Scripting.Dictionary

 dic.Add Cells(2, 1).Value, 2 ' 2 is the row number

 Debug.Print Cells(dic.Item(1), 2).Value
 Debug.Print Cells(dic.Item(1), 3).Value

Prints "a" and "aa" on my Excel.

WBD

I get a Run-time error '1004' on this line:

Debug.Print Cells(dic.Item(1), 2).Value

 

[wideboydixon]

Hmmm. Odd. Try:

Debug.Print dic.Keys(0)

That should show what's in the first key.

WBD

 

[tiredofit]

Hmmm. Odd. Try:

Debug.Print dic.Keys(0)

That should show what's in the first key.

WBD

It shows 1.

BTW, your original suggestionnow worked, AFTER I closed down Excel and recreated it!