excel - VBA:从字典中获取值

Question

我尝试在 VBA 中使用字典。 Microsoft Scripting Runtime 被激活，我设法用我想要的数据填充它(For Each 循环向我展示了同样多的数据)。但后来我没有进一步

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

Debug.Print 的输出声明:

dict.Keys(20): 101010074

dict.Items(20): 22

dict.Exists('101010074'): 假

dict.Exists(101010074): 假

字典('101010074'):

字典(101010074):

编辑: VarType(dict.Keys(20)): 8 (= string)

所以如果我没看错(可能不是)，有一个键“101010074”，但是为什么 dict.Exists("101010074")返回 False ?
For Each 的输出循环 Debug.Print key, dict(key) :

Answer 1

您在字典中添加 key 的方式是将 key 设置为 Range对象而不是 Value .改用以下内容

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