Set dic=CreateObject("scripting.dictionary")With ActiveSheet arr=.UsedRange For i=2ToUBound(arr)'dic(arr(i,1))没有值是默认是0,通过下面方法对每一个水果的销量进行累加。dic(arr(i,1))=dic(arr(i,1))+arr(i,2)Next '使用copy方法,将表头复制到e1,f1单元格.Range("a1:b1").Copy.Range("e1"...
arr1 = ActiveSheet.Range("B3:B12") arr2 = ActiveSheet.Range("C3:C12") Set dic = CreateObject("Scripting.Dictionary") For i = 1 To UBound(arr1) - 1 dic.Add arr1(i, 1), arr2(i, 1) Next i Me.ListBox1.Clear For i = 1 To dic.Count With Me.ListBox1 .AddItem .List(i - ...
arrA=Range("A1").Resize(rowA,1).Value '将A列数据记录到字典中,并更新Item的值+1For i=2To rowAd(VBA.CStr(arrA(i,1)))=VBA.CLng(d(VBA.CStr(arrA(i,1)))+1Next '输出Range("B1").Resize(d.Count,1).Value=Application.WorksheetFunction.Transpose(d.keys)Range("C1").Resize(d.Count,1)....
'创建字典对象 Set dict = CreateObject("Scripting.Dictionary") '设置工作表对象 Set wks = Worksheets("Sheet2") '工作表最后一行 lngLastRow = wks.Range("A" & Rows.Count).End(xlUp).Row '错误处理 On Error Resume Next '遍历工作表并在字典中添加数据 For i = 1 To lngLastRow dict.Add wks....
Set mydic = CreateObject("Scripting.Dictionary") '字典 TT = Cells(Rows.Count, 1).End(xlUp).Row For Each ran In Sheets("59").Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)If ran.Value <> "" Then If Not mydic.exists(ran.Value) Then mydic.Add ran.Value, 1 Else mydic...
myarr = Range("a2:f" & Range("a2").End(xlDown).Row)上述代码实现了数据数组装载，。3 For i = 1 To UBound(myarr)'这里利用了字典的嵌套 If Not mydic.exists(myarr(i, 1)) Then Set mydic(myarr(i, 1)) = CreateObject("Scripting.Dictionary")End If If Not mydic(myarr(i, 1))....
最后再通过遍历mydic.keys()(得到一个存有所有key的一维数组)的每一个元素,逐个输出到另一列,也可以选择覆盖原则,达到去除重复项的效果。也可以将数组一次性写入一个单元格区域(range的长度需要跟字典的长度一致,否则无法写入成功),代码示例如下: 1SubremoveDuplicates()2DimmyDicAsObject, iAsInteger, shtAs...
Dim arr, d As Object, k, t, i&, lc%, rng As Range, c% c = Application.InputBox("请...
Set d=New Dictionary DimarrA()As Variant Dim rowA As Long Dim i As Long '获取A列的最后一行行号 rowA=Cells(Cells.Rows.Count,1).End(xlUp).Row '将A列的数据存放到数组中 arrA=Range("A1").Resize(rowA,1).Value '将A列数据记录到字典中 ...
Set mydic = CreateObject("Scripting.Dictionary")myarr1 = Range([A2], [A65536].End(xlUp))myarr2 = Range([B2], [B65536].End(xlUp))For i = 1 To UBound(myarr1)'将键值赋值为0 mydic(myarr1(i, 1)) = 0 Next For j = 1 To UBound(myarr2)'如果数组2在字典中存在相应的键，那么...