|
- Private Sub cmd_1_Click()
- Dim r%, i%
- Static arr, brr
- Static x%, y%
- Dim crr, drr
- Dim d As Object
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- If cmd_1.Caption = "开始" Then
- cmd_1.Caption = "停止"
- Else
- cmd_1.Caption = "开始"
- End If
- If cmd_1.Caption = "停止" Then
- flg = True
- With Worksheets("人员名单列表")
- r = .Cells(.Rows.Count, 1).End(xlUp).Row
- brr = .Range("a1:b" & r)
- For i = 1 To UBound(brr)
- d(brr(i, 1)) = ""
- If Len(brr(i, 2)) <> 0 Then
- d1(brr(i, 1)) = ""
- End If
- Next
- End With
- With Worksheets("sheet1")
- arr = .Range("b4:f26")
- For i = 1 To UBound(arr)
- For j = 1 To UBound(arr, 2)
- If Len(arr(i, j)) <> 0 Then
- If d.exists(arr(i, j)) Then
- d.Remove (arr(i, j))
- End If
- End If
- Next
- Next
- For i = UBound(arr) To 1 Step -1
- For j = UBound(arr, 2) To 1 Step -1
- If Len(arr(i, j)) = 0 Then
- x = i
- y = j
- End If
- Next
- Next
- i = 23
- For j = 1 To UBound(arr, 2)
- If Len(arr(i, j)) <> 0 Then
- If d1.exists(arr(i, j)) Then
- d1.Remove (arr(i, j))
- End If
- End If
- Next
- End With
- crr = d.keys
- drr = d1.keys
- Do While flg
- DoEvents
- m = Int(Rnd() * (UBound(crr) + 1))
- If x < 23 Then
- If Not d1.exists(crr(m)) Then
- Worksheets("sheet1").Range("d2") = crr(m)
- End If
- Else
- If d1.Count > 0 Then
- m = Int(Rnd() * (UBound(drr) + 1))
- Worksheets("sheet1").Range("d2") = drr(m)
- Else
- Worksheets("sheet1").Range("d2") = crr(m)
- End If
- End If
- Loop
- Else
- flg = False
- Worksheets("sheet1").Cells(x + 3, y + 1) = Worksheets("sheet1").Range("d2")
- End If
- End Sub
复制代码 |
|