|
Sub 随机分班()
Dim ar, br(), sht As Worksheet
Set d = CreateObject("scripting.dictionary")
Set dd = CreateObject("scripting.dictionary")
Sheet1.Activate
ar = [a1].CurrentRegion
x = 3
For i = 2 To UBound(ar)
n = Int(Rnd * x) + 1 'Rnd 函数返回小于 1 但大于或等于 0 的值。
d(n) = d(n) + 1
Do While d(n) > 8
n = Int(Rnd * x) + 1 '
d(n) = d(n) + 1
Loop
dd(n) = dd(n) & "," & i
Next i
Application.ScreenUpdating = False
For Each sht In Worksheets
If sht.Name <> "题目要求" Then
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
Next sht
For Each k In dd.keys
ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
a = Split(dd(k), ",")
For i = 1 To UBound(a)
y = a(i)
m = m + 1
For j = 1 To UBound(ar, 2)
br(m, j) = ar(y, j)
Next j
Next i
Sheets.Add(After:=Sheets(Sheets.Count)).Name = k & "班"
[a1:c1] = Array("ID", "科目", "成绩")
ActiveSheet.[a2].Resize(UBound(a) - 1, 3) = br
m = 0
Next k
Sheet1.Activate
Application.ScreenUpdating = True
End Sub
|
评分
-
查看全部评分
|