VBA Excel 将值添加到字典
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25160909/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel adding values to dictionary
提问by RonTheBear
Alright, maybe I've just been looking at this for too long but I keep getting an expected expression error, which I believe is just a syntax issue but I'm not entirely sure. I'm looping through one sheet and adding unique values of one column as keys to the dictionary while adding the numbers that correspond to the keys together. For example, if I have:
好吧，也许我只是看这个太久了，但我一直收到预期的表达式错误，我认为这只是一个语法问题，但我不完全确定。我正在遍历一张纸并将一列的唯一值添加为字典的键，同时将与键对应的数字添加在一起。例如，如果我有：
A 2
B 3
B 4
A 5
C 6
A 2
B 3
B 4
A 5
C 6
I want the dictionary to look like:
我希望字典看起来像：
A 7
B 7
C 6
A 7
B 7
C 6
Here's my code, any help is appreciated.
这是我的代码，任何帮助表示赞赏。
Sub Name()
Dim rng As Range
Dim x As Integer
Dim ranga As String
Dim dico As Dictionary
Set dico = New Dictionary
Dim var As Variant
Dim lastrow As Integer
With Worksheets("Sheet1")
lastrow = Range("A" & .Rows.Count).End(xlUp).Row
ranga = "C6" & ":" & "C" & CStr(lastrow)
Set rng = Range(ranga)
For Each var In rng.Cells
If dico.Exists(var.Value) Then
dico(var.Value) = dico(var.Value) + var.Offset(0, 4).Value
Else
dico.add var.Value, var.Offset(0, 4).Value
End If
Next var
End With
With Worksheets("Sheet2")
Set rng = Range("A2")
Dim i As Integer
i = 0
For Each var In dico.Keys
rng.Offset(i).Value = var
rng.Offset(i, 1).Value = dico(var)
Next var
End With
End Sub
回答by Frank
I am new to stackoverflow, so I am a but unsure of the appropriate etiquette, but here is a working solution.
我是 stackoverflow 的新手，所以我不确定适当的礼仪，但这里有一个可行的解决方案。
Public Sub dict_counter()
Dim counter As New Dictionary
Dim key As Range: Set key = ThisWorkbook.Sheets("sheet1").Range("A1")
While Not IsEmpty(key)
If counter.Exists(key.Value) Then
counter(key.Value) = counter(key.Value) + key.Offset(ColumnOffset:=1)
Else
counter(key.Value) = key.Offset(ColumnOffset:=1)
End If
Set key = key.Offset(RowOffset:=1)
Wend
'Obviously you can output the dict contents to whatever location
'is convenient
Dim k As Variant
For Each k In counter
Debug.Print k; counter(k)
Next k
End Sub
回答by neelsg
Instead of
代替
dico(var.Value) = dico(var.Value) + var.Offset(0, 4).Value
It should be
它应该是
dico.Item(var.Value) = dico(var.Value) + var.Offset(0, 4).Value
See MSDN
见MSDN
Also, if you use With, you have to actually put leading .'s where you want to use methods or properties of it like this:
此外，如果您使用With，则必须实际将leading 放在.要使用它的方法或属性的位置，如下所示：
With Worksheets("Sheet1")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
See MSDN
见MSDN
