|
- Sub Macro1()
- Dim arr, brr(1 To 10, 1 To 3), d, i&, j&, s%
- Set d = CreateObject("scripting.dictionary")
- For i = 1 To Sheets.Count
- If Sheets(i).Name <> "总表" Then
- arr = Sheets(i).UsedRange
- For j = 1 To UBound(arr)
- If arr(j, 1) <> "" Then d(arr(j, 1)) = d(arr(j, 1)) + 1
- Next
- End If
- Next
- a = d.keys: b = d.items
- For i = 1 To d.Count
- x = Application.Large(d.items, i)
- For j = 0 To d.Count - 1
- If b(j) = x And s < 11 Then
- s = s + 1
- brr(s, 1) = s
- brr(s, 2) = a(j)
- brr(s, 3) = x
- End If
- Next
- Next
- Range("a2").Resize(s, 3) = brr
- End Sub
复制代码 |
|