|
- Sub 查找1()
- Dim arr, ar, d, i&, m%, j%, k%, s&
- Set d = CreateObject("scripting.dictionary")
- w = Array("期中", "期末")
- Sheets("前10名").Activate
- For m = 0 To UBound(w)
- arr = Sheets(w(m)).Range("a1").CurrentRegion
- ReDim ar(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 3 To UBound(arr)
- If Not d.Exists(arr(i, 3)) Then
- d(arr(i, 3)) = i
- Else
- d(arr(i, 3)) = d(arr(i, 3)) & "," & i
- End If
- Next
- s = 0
- For j = 1 To 10 '前10名
- x = Application.Large(d.Keys, j)
- y = Split(d(x), ",")
- For k = 0 To UBound(y)
- s = s + 1
- For l = 1 To UBound(arr, 2)
- ar(s, l) = arr(y(k), l)
- Next
- Next
- Next
- lie = IIf(m = 0, 1, 7)
- Cells(4, lie).Resize(s, UBound(ar, 2)) = ar
- d.RemoveAll
- Next
- End Sub
- Sub 查找2()
- Dim arr, brr, m%, i&, j%
- ReDim brr(1 To 50000, 1 To 5)
- w = Array("期中", "期末")
- Sheets("高于150分").Activate
- For m = 0 To UBound(w)
- arr = Sheets(w(m)).Range("a1").CurrentRegion
- ReDim ar(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 3 To UBound(arr)
- If arr(i, 5) > 120 Or arr(i, 5) < 60 Then
- s2 = s2 + 1
- For j = 1 To UBound(arr, 2)
- brr(s2, j) = arr(i, j)
- Next
- End If
- Next
- Next
- Range("a4").Resize(s2, 5) = brr
- End Sub
复制代码 |
|