|
Sub 考场安排()
With Sheets("设置")
r = .Cells(Rows.Count, 7).End(xlUp).Row
If r < 3 Then MsgBox "请先设置各个考场人数!": End
ar = .Range("f2:h" & r)
End With
With Sheets("学生信息")
rs = .Cells(Rows.Count, 2).End(xlUp).Row
If rs < 2 Then MsgBox "学生信息为空!": End
.Range("c2:e" & rs) = Empty
br = .Range("a1:e" & rs)
Dim cr()
ReDim cr(1 To UBound(br), 1 To UBound(br, 2))
For i = 2 To UBound(br)
br(i, 5) = Application.RandBetween(2, rs - 1)
Next i
For i = 2 To UBound(br)
For s = i + 1 To UBound(br)
If br(i, 5) > br(s, 5) Then
For j = 1 To UBound(br, 2)
k = br(i, j)
br(i, j) = br(s, j)
br(s, j) = k
Next j
End If
Next s
Next i
m = 1
For i = 2 To UBound(ar)
sl = ar(i, 2)
For s = 1 To sl
m = m + 1
br(m, 3) = ar(i, 1)
br(m, 4) = s
Next s
Next i
.Range("a1:d" & rs) = br
End With
MsgBox "ok!"
End Sub
|
|