|
- Sub 提取()
- arr = Range("a7:b" & [a65536].End(3).Row)
- n = UBound(arr) '总组数
- Set d = CreateObject("scripting.dictionary")
- ytq = Application.WorksheetFunction.Sum(Rows(3)) '已提取量
- n = n - ytq '剩下的总提取量
- tql = Val(InputBox("看看谁被抽到" & Chr(10) & "多少个?", "市名额", 17)) '本次提取量
- If tql > n Then tql = n '如果提取量大于剩余量,那么提取量=剩余量
-
- ReDim brr(1 To tql, 1 To 2)
- If ytq = 0 Then '未进行过提取
- c = 1
- Else '已进行过提取
- c = Cells(3, 256).End(xlToLeft).Column
- tqrr = Range([d7], Cells(UBound(arr) + 6, c + 1)) '已提取的组
- For j = 1 To UBound(tqrr, 2) Step 3 '已提取过的内容进字典
- For i = 1 To UBound(tqrr)
- If tqrr(i, j) <> "" Then
- d(tqrr(i, j)) = ""
- End If
- Next
- Next
-
- For i = 1 To UBound(arr) '剩下可提取的内容放到数组arr的前n位
- If Not d.exists(arr(i, 1)) Then
- kk = kk + 1
- arr(kk, 1) = arr(i, 1): arr(kk, 2) = arr(i, 2)
- End If
- Next
- End If
-
- For i = 1 To tql '在arr中的前n位中提取tql个数
- k = Int(Rnd * n) + 1 '生成1--n的随机数
- brr(i, 1) = arr(k, 1): arr(k, 1) = arr(n, 1)
- brr(i, 2) = arr(k, 2): arr(k, 2) = arr(n, 2)
- n = n - 1
- Next
-
- Cells(3, c + 3) = tql
- Cells(6, c + 3).Resize(1, 2) = Array("工号唯一", "体重公斤")
- Cells(7, c + 3).Resize(tql, 2) = brr
- End Sub
复制代码 |
|