VBA字典是复制键-Java 学习之路

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

enter image description here

我试图循环键，添加键和项目 . 如果密钥存在，我想将项添加到它 . 发生的事情是密钥被重复 . 在下面的示例中，我最终得到了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

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

回复于 2024-05-05T04:55:41+08:00

VBA字典是复制键

1 回答

相关问题