VBA 字典正在复制键

Question

遇到麻烦，需要一些帮助。我创建了一个简单的示例来学习 VBA 中的字典，并且已经遇到了一个问题。我有以下设置:

我正在尝试遍历键，添加键和项目。如果 key 存在，我想将项目添加到其中。相反发生的是 key 被复制。在下面的示例中，我最终得到了 2 个苹果键而不是 1 个。我做错了什么？任何帮助是极大的赞赏。相关代码如下:

Dim wkb As Workbook
Dim ws As Worksheet
Dim dict As Scripting.Dictionary

Set wkb = ActiveWorkbook
Set ws = wkb.ActiveSheet

'clearing old totals
ws.Range("C8:C9").ClearContents

Set dict = New Scripting.Dictionary
dict.CompareMode = vbTextCompare

For i = 3 To 6
    If dict.Exists(ws.Cells(i, "B").Value) = False Then
        MsgBox "doesnt exist, adding " & ws.Cells(i, "B")
        dict.Add ws.Cells(i, "B"), ws.Cells(i, "C")
    ElseIf dict.Exists(ws.Cells(i, "B").Value) Then
        MsgBox "exists"
        dict.Item(ws.Cells(i, "B")) = dict.Item(ws.Cells(i, "B")) + ws.Cells(i, "C").Value
    End If
Next i

MyArray = dict.Keys
MsgBox "Keys are: " & Join(MyArray, ";")

MyArray = dict.Items
MsgBox "Items are: " & Join(MyArray, ";")

For Each k In dict.Keys
    ws.Range("C8") = ws.Range("C8") + dict.Item(k)
    If k = "Apples" Then
        ws.Range("C9") = ws.Range("C9") + dict.Item(k)
    End If
Next

Answer 1

您当前将单元格添加为键，而不是单元格的 Value，并且单元格 B6 不是单元格 B3 - 它们在以下位置具有不同的 .Row 属性最少。

您应该将代码更改为:

For i = 3 To 6
    If dict.Exists(ws.Cells(i, "B").Value) = False Then
        MsgBox "doesnt exist, adding " & ws.Cells(i, "B").Value
        dict.Add ws.Cells(i, "B").Value, ws.Cells(i, "C").Value
    ElseIf dict.Exists(ws.Cells(i, "B").Value) Then
        MsgBox "exists"
        dict.Item(ws.Cells(i, "B").Value) = dict.Item(ws.Cells(i, "B").Value) + ws.Cells(i, "C").Value
    End If
Next i