scripting.dictionary method

[idnoidno]

Table 1 is the data, I would like to use the scripting.dictionary method to calculate the each sum of item.I want to learn the dictionary code.can someone give me a help?


19535

[mana]

Option Explicit


Sub test()
Dim dic As Object
Dim r As Range
Dim i As Long, j As Long
Dim v()
Dim n As Long
Dim s

Set dic = CreateObject("scripting.dictionary")

Set r = Cells(1).CurrentRegion
ReDim v(1 To r.Rows.Count, 1 To 7)

For i = 1 To r.Rows.Count
s = r.Cells(i, 1).Value
If Not dic.exists(s) Then
n = n + 1
dic(s) = n
v(n, 1) = s
End If
n = dic(s)
For j = 2 To 7
v(n, j) = v(n, j) + r.Cells(i, j).Value
Next
Next

With Worksheets(2).Cells(1)
.CurrentRegion.ClearContents
.Resize(dic.Count, UBound(v, 2)).Value = v
End With

End Sub

[mana]

you can use consolidate method instead of dictionary



Option Explicit


Sub test2()
Dim s As Range, d As Range

Set s = Sheets(1).Cells(1).CurrentRegion
Set d = Sheets(2).Cells(1).Resize(, 7)

d.CurrentRegion.Offset(1).ClearContents
d.Consolidate s.Address(, , xlR1C1, True), xlSum, True, True

End Sub

[idnoidno]

ReDim v(1 To r.Rows.Count, 1 To 8)....

For j = 2 To 8


Thanks for your help, Mr Mana.
I am learning the Dictionary method for vba now, can you give some advice of that?

[idnoidno]

19537

If I want to use a & b to calculate the result in right table, does the dictionary method still apply?

[SamT]

For much info: http://www.snb-vba.eu/VBA_Dictionary_en.html