[已解决]VBA把字典改写用数组数组 A列重复数字

laoau138

VBA把字典改写用数组数组   A列重复数字


Sub 把字典改写用数组()
    Dim r%, s$, i%, j%, d As Object, arr, brr
    r = Range("a65536").End(xlUp).Row
    Set d = CreateObject("scripting.dictionary")
    For i = 1 To r
        d(Cells(i, 1).Value) = d(Cells(i, 1).Value) + 1
    Next
    arr = d.keys
    brr = d.items
    For j = 0 To UBound(brr)
        If brr(j) > 1 Then s = s & " " & arr(j)
    Next
    [d9] = Replace(Trim(s), " ", ",")
End Sub

最佳答案

月排行榜 / 总排行榜

windyjw007

2017-5-30 09:24

结果顺序不太一样。。。{:1512:}

1. Sub test()
   
2.     Dim ar
   
3.     Dim sr As String
   
4.     Dim i As Long, j As Long, k As Long
   
5.     ar = Cells(1, 1).CurrentRegion
   
6.     For i = 1 To UBound(ar)
   
7.         For j = 1 To i - 1
   
8.             If ar(i, 1) = ar(j, 1) Then k = k + 1
   
9.         Next j
   
10.         If k = 1 Then sr = sr & "," & ar(i, 1)
    
11.         k = 0
    
12.     Next i
    
13.     Cells(10, 4) = Mid(sr, 2)
    
14. End Sub

复制代码

跳转到最佳答案楼层

windyjw007

结果顺序不太一样。。。{:1512:}

1. Sub test()
   
2.     Dim ar
   
3.     Dim sr As String
   
4.     Dim i As Long, j As Long, k As Long
   
5.     ar = Cells(1, 1).CurrentRegion
   
6.     For i = 1 To UBound(ar)
   
7.         For j = 1 To i - 1
   
8.             If ar(i, 1) = ar(j, 1) Then k = k + 1
   
9.         Next j
   
10.         If k = 1 Then sr = sr & "," & ar(i, 1)
    
11.         k = 0
    
12.     Next i
    
13.     Cells(10, 4) = Mid(sr, 2)
    
14. End Sub

复制代码

laoau138

windyjw007 发表于 2017-5-30 09:24
结果顺序不太一样。。。

用这种方法如何写

再定义一个同样大小的数组，
用双重循环在对应行保存相应数字的重复次数，
例如brr(i)存储arr(i)的重复次数，类似COUNTIF的结果，然后扫描brr数组得到结果。

laoau138

windyjw007 发表于 2017-5-30 09:24
结果顺序不太一样。。。

用这个方法如何写