|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
各科前N名
- Sub test3()
- Dim r%, i%
- Dim arr, brr
- mc = Application.InputBox(prompt:="请输入要提取各科成绩的最大名次", Title:="输入提示", Default:=10, Type:=1)
- ReDim brr(1 To mc * 2, 1 To 9)
- With Worksheets("年级总排名")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("a2:j" & r)
- End With
- For j = 4 To 6
- fs = Application.Large(Application.Index(arr, 0, j), mc)
- m = 0
- n = j * 3 - 11
- For i = 1 To UBound(arr)
- If arr(i, j) >= fs Then
- m = m + 1
- brr(m, n) = arr(i, 1)
- brr(m, n + 1) = arr(i, 3)
- brr(m, n + 2) = arr(i, j)
- End If
- Next
- Next
- With Worksheets("提取各科前N名")
- .UsedRange.Offset(1, 0).Clear
- With .Range("a2").Resize(m, UBound(brr, 2))
- .Value = brr
- ' .Sort key1:=.Range("i3"), order1:=xlAscending, Header:=xlNo
- End With
- For j = 1 To 7 Step 3
- r = .Cells(.Rows.Count, j).End(xlUp).Row
- .Cells(1, j).Resize(r, 3).Sort key1:=.Cells(2, j + 2), order1:=xlDescending, Header:=xlYes
- Next
- r = .UsedRange.Find(what:="*", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
- With .Range("a1").Resize(r, 9)
- With .Font
- .Size = 10
- .Name = "宋体"
- End With
- .Borders.LineStyle = xlContinuous
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
-
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|