View Full Version : [SOLVED:] scripting.dictionary method
idnoidno
06-17-2017, 07:01 PM
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
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
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
06-18-2017, 06:07 AM
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
06-18-2017, 07:22 AM
19537
If I want to use a & b to calculate the result in right table, does the dictionary method still apply?
For much info: http://www.snb-vba.eu/VBA_Dictionary_en.html
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.