grf1973 发表于 2015-10-16 16:55
n是可提取的总组数,每提掉一个,n=n-1
搞了二天,强烈不对啊。- Sub 不对的()
- On Error GoTo Err
- arr = Range("A7:E" & [A1048576].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) & "多少个?", "市名额", _
- Application.WorksheetFunction.RandBetween(1, Val(UBound(arr)))))
- If tql > n Then tql = n '如果提取量大于剩余量,那么提取量=剩余量
- ReDim brr(1 To tql, 1 To 5)
- If ytq = 0 Then '未进行过提取
- c = 1
- Else '已进行过提取
- c = Cells(3, 256).End(xlToLeft).Column
- tqrr = Range([G7], Cells(UBound(arr) + 6, c + 1)) '已提取的组
- For j = 1 To UBound(tqrr, 2) Step 6 '已提取过的内容进字典
- 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, 4)) Then '怎么不对??????????????????????????????????????????????
- kk = kk + 1
- arr(kk, 1) = arr(i, 1)
- arr(kk, 2) = arr(i, 2)
- arr(kk, 3) = arr(i, 3)
- arr(kk, 4) = arr(i, 4)
- arr(kk, 5) = arr(i, 5)
- 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)
- brr(i, 3) = arr(k, 3): arr(k, 3) = arr(n, 3)
- brr(i, 4) = arr(k, 4): arr(k, 4) = arr(n, 4)
- brr(i, 5) = arr(k, 5): arr(k, 5) = arr(n, 5)
- n = n - 1
- Next
- Cells(3, c + 6) = tql
- Cells(6, c + 6).Resize(1, 5) = Array("工号唯一", "体重公斤", "入厂日期", "姓名", "血液")
- Cells(7, c + 6).Resize(tql, 1).NumberFormatLocal = "@" '根据需要哪些列要强制文本!
- Cells(7, c + 6).Offset(0, 2).Resize(tql, 1).NumberFormatLocal = "yyyy-mm-dd"
- Cells(7, c + 6).Resize(tql, 5) = brr
- Cells(7, c + 6).Resize(tql, 5).EntireColumn.AutoFit
- Err:
- End Sub
复制代码 |