|
张雄友 发表于 2015-10-17 18:46
搞了二天,强烈不对啊。
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 + 5)) '已提取的组
For j = 4 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
|
评分
-
查看全部评分
|