本帖最后由 陈丹青 于 2014-4-29 10:39 编辑
谢谢点评,请多多指教
Sub 定班() '女先升后降,男先降后升,平均分较为均匀
Range("i2").Select
Range("A1:j" & [B65536].End(3).Row).Sort Key1:=Range("B2"), Order1:=xlDescending, Key2:= _
Range("C2"), Order2:=xlDescending, Header:=xlGuess
Range("D1") = "班"
Dim mm As Long
On Error Resume Next
mm = InputBox("请你输入要分几班:", "班别数目提示息,请输入正整数", "0")
If mm <= 0 Then
MsgBox "班别数目不符合要求"
Exit Sub
End If
Dim arr, j, s, a As Long
j = Range("B:B").Find("女", , , , , xlPrevious).Row ' Cells(Rows.Count, 2).End(xlUp).Row '第二列有数据的行
Range("D2:D" & [d65536].End(3).Row).ClearContents 'M列到有数据的行内容清除
arr = Range("a2:D" & j).Value '选择A2到K最后
For i = 1 To UBound(arr) Step mm * 2
For j = 1 To mm
arr(i + j - 1, 4) = j '写到4列
arr(i + j + mm - 1, 4) = mm - j + 1 '写到4列
Next
Next
Range("a2").Resize(UBound(arr), UBound(arr, 2)).Value = arr
a = Application.WorksheetFunction.CountIf(Range("B1:B65536"), "女") + 1
j = Range("B:B").Find("男", , , , , xlPrevious).Row ' Cells(Rows.Count, 2).End(xlUp).Row '第二列有数据的行
arr = Range("a2:D" & j).Value '选择A2到K最后
For i = a To UBound(arr) Step mm * 2
For j = 1 To mm
arr(i + j + mm - 1, 4) = j '写到4列
arr(i + j - 1, 4) = mm - j + 1 '写到4列
Next
Next
Range("a2").Resize(UBound(arr), UBound(arr, 2)).Value = arr
End Sub
|