CODEWITHSUNDEEP
excelvbadictionary
NoNameNo123
NoNameNo123

Reputation: 665

VBA: Get value from dictionary

I tried using dictionaries in VBA. Microsoft Scripting Runtime is activated and I managed to fill it with the data I want (the For Each loop shows me as much). But then I didn't get any further 

Sub Report2()

    Dim wbAggr As Workbook, wbMonat As Workbook
    Dim wsAggr As Worksheet
    Dim iRow As Long, lRowAggr As Long
    Dim dict As Scripting.Dictionary
    Dim key As Variant


    Set dict = New Scripting.Dictionary
    Set wbAggr = ThisWorkbook
    Set wsAggr = ThisWorkbook.Sheets(1)

    lRowAggr = wsAggr.Cells(Rows.Count, 1).End(xlUp).Row

    For iRow = 2 To lRowAggr
        dict.Add wsAggr.Cells(iRow, "K"), iRow
    Next iRow


    For Each key In dict.Keys  '-> returns 2500 key+item pairs
        Debug.Print key, dict(key)
    Next key

    Debug.Print "dict.Keys(20): " & dict.Keys(20)
    Debug.Print "dict.Items(20): " & dict.Items(20)
    Debug.Print "dict.Exists('101010074'): " & dict.Exists("101010074")
    Debug.Print "dict.Exists(101010074): " & dict.Exists(101010074)
    Debug.Print "dict('101010074'): " & dict("101010074")
    Debug.Print "dict(101010074): " & dict(101010074)
    Debug.Print "VarType(dict.Keys(20)): " & VarType(dict.Keys(20))
End Sub

Output for the Debug.Print statements:

So if I'm reading this right (probably not), there is a key "101010074", but then why does dict.Exists("101010074") return False?

Output of For Each loop with Debug.Print key, dict(key):

Output of <code>Debug.Print key, dict(key)</code>

Upvotes: 1

Views: 1608

Answers (1)

Tom
Tom

Reputation: 9878

The way you're adding your key the dictionary is setting the key to the Range object and not the Value. Use the following instead

For iRow = 2 To lRowAggr
    dict.Add wsAggr.Cells(iRow, "K").Value2, iRow
Next iRow

Upvotes: 2

Related Questions