|
发表于 2014-2-23 22:16
|
显示全部楼层
本楼为最佳答案
- Sub aa()
- Dim arr2
- Application.ScreenUpdating = False
- Dim d As Object
- Dim d1 As Object
- For m = 8 To Range("r65536").End(xlUp).Row
- x = Cells(m, 18)
- Set d1 = CreateObject("scripting.dictionary")
- For i = 1 To Len(x)
- b = Mid(x, i, 1)
- If Not d1.exists(b) Then d1.Add b, 1 Else d1(b) = d1(b) + 1
- Next i
- p1 = ""
- For i = 0 To 9
- If InStr(x, i) = 0 Then p1 = p1 & i
- Next i
- a = d1.keys: b = d1.items
- Range("ae1").Resize(d1.Count, 1) = Application.WorksheetFunction.Transpose(a)
- Range("af1").Resize(d1.Count, 1) = Application.WorksheetFunction.Transpose(b)
- Range("AE1:AF" & d1.Count).Sort Key1:=Range("AF1"), Order1:=xlDescending, Key2:=Range("AE1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1
- p = Range("ae1")
- For i = 2 To d1.Count
- If Cells(i, "af") = Cells(i - 1, "af") Then p = p & Cells(i, 31) Else p = p & " " & Cells(i, 31)
- Next i
- Range("AE1:AF" & d1.Count).ClearContents
- If p1 <> "" Then
- arr2 = Split(p & " " & p1, " ")
- Cells(m, 20) = p & " " & p1
- ' Cells(m, 21).Resize(, UBound(arr2) + 1).Value = arr2
- ' Cells(m, 20) = p & " " & p1
- Else
- Cells(m, 20) = p '--------------
- arr2 = Split(p, " ")
- ' Cells(m, 21).Resize(, UBound(arr2) + 1).Value = arr2
- ' Cells(m, 20) = p & " " & p1
- End If
- Cells(m, 21).Resize(, UBound(arr2) + 1).Value = arr2
- Cells(m, 20) = p & " " & p1
- Next m
- Set d1 = Nothing
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|