|
发表于 2016-2-3 12:38
|
显示全部楼层
本楼为最佳答案
- Sub 成绩统计()
- Dim arr, i&, j&, d, s, rmax, x
- Set d = CreateObject("scripting.dictionary")
- With Sheets(1)
- s = .[k1].Value
- rmax = .Cells(Rows.Count, 1).End(xlUp).Row
- arr = .Range("a4:k" & rmax)
- For i = 1 To UBound(arr)
- x = arr(i, 1)
- If x = s Then
- If Not d.exists(x) Then
- Set d(x) = .Cells(i + 3, 1).Resize(1, 11)
- Else
- Set d(x) = Union(d(x), .Cells(i + 3, 1).Resize(1, 11))
- End If
- End If
- Next
- With Sheets(2)
- .[a4:k10000].ClearContents
- If d.exists(s) Then d(s).Copy .[a4]
- rmax = .Cells(Rows.Count, 1).End(3).Row
- .Range("a4:k" & rmax).Sort key1:=.[c4], order1:=xlDescending, key2:=.[b4], order2:=xlAscending, Header:=xlNo
- End With
- End With
- End Sub
复制代码 |
|