- Sub yx()
- Dim r%, i%
- Dim arr, brr
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- With Worksheets("成绩")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a4:s" & r)
- End With
- For i = 1 To UBound(arr)
- If arr(i, 17) > 800 Then
- If Not d.exists(arr(i, 2)) Then
- m = 1
- ReDim brr(1 To 5, 1 To m)
- Else
- brr = d(arr(i, 2))
- m = UBound(brr, 2) + 1
- ReDim Preserve brr(1 To 5, 1 To m)
- End If
- brr(1, m) = arr(i, 1)
- brr(2, m) = arr(i, 3)
- brr(3, m) = arr(i, 17)
- brr(4, m) = arr(i, 18)
- brr(5, m) = arr(i, 19)
- d(arr(i, 2)) = brr
- End If
- Next
- With Worksheets("优秀")
- .UsedRange.Offset(3, 0).ClearContents
- n = 1
- For Each aa In d.keys
- brr = d(aa)
- ReDim crr(1 To UBound(brr, 2), 1 To UBound(brr))
- For i = 1 To UBound(brr)
- For j = 1 To UBound(brr, 2)
- crr(j, i) = brr(i, j)
- Next
- Next
- .Cells(2, n + 2) = aa
- With .Cells(4, n).Resize(UBound(crr), UBound(crr, 2))
- .Value = crr
- End With
- .Cells(4, n).Resize(UBound(crr), UBound(crr, 2)).Sort key1:=.Cells(4, n + 3), order1:=xlAscending, Header:=xlGuess
- n = n + 6
- Next
- End With
- End Sub
复制代码 |