'a:c20,h8,b10,a2 -->输出A工作表,20个C类题,8个H类题,,,合计必须为40
Option Explicit
Sub test()
Dim data, dic(1), i, j, p, sht, s, t, m, n, cnt, a, b, mark
mark = Split("a:c20,h8,b10,a2|b:f10,g8,c10,b10,a2|c:c30,b8,a2", "|") '可以生成多套题
For i = 0 To UBound(dic)
Set dic(i) = CreateObject("scripting.dictionary")
Next
data = Sheets("题库").[a1].CurrentRegion.Offset(1).Resize(, 2)
For i = 1 To UBound(data, 1) - 1
If Left(data(i, 1), 1) <> Left(data(i + 1, 1), 1) Then
dic(0)(Left(data(i, 1), 1)) = Array(p + 1, i)
p = i
End If
Next
Randomize
For i = 0 To UBound(mark)
mark(i) = UCase(mark(i))
t = Split(mark(i), ":")
sht = t(0)
If Not dic(0).exists(sht) Then MsgBox sht: Exit Sub
t = Split(t(1), ",")
b = 5: dic(1).RemoveAll: m = 0
ReDim arr(1 To 20, 1 To 1) As String
For j = 0 To UBound(t)
s = Left(t(j), 1)
If Not dic(0).exists(s) Then MsgBox s: Exit Sub
n = Val(Mid(t(j), 2))
p = dic(0)(s): cnt = p(1) - p(0) + 1
Do
a = Int(Rnd * cnt) + p(0)
If Not dic(1).exists(data(a, 1)) Then
m = m + 1: n = n - 1: dic(1)(data(a, 1)) = n
arr(m, 1) = data(a, 1) & data(a, 2)
If m Mod 20 = 0 Then
Sheets(sht).Cells(5, b).Resize(UBound(arr, 1)) = arr
ReDim arr(1 To 20, 1 To 1) As String
b = b + 4: m = 0
End If
End If
DoEvents
Loop Until n = 0
Next
Next
End Sub |