|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
为了避免使用Do语句造成死循环,采用乱序排序模拟随机取值,请参考:- Sub Macro1()
- Dim arr(1 To 2), brr(), i&, j&, m&, n&, r&, l&, lr&, ii&, v&, d As Object, ds As Object
- Application.ScreenUpdating = False
- Set d = CreateObject("scripting.dictionary")
- Set ds = CreateObject("scripting.dictionary")
- lr = Range("A65536").End(xlUp).Row - 1
- ReDim brr(1 To lr, 1 To 1)
- For i = 1 To lr
- brr(i, 1) = Int((Rnd * lr) + 1)
- Next
- [e2].Resize(lr) = brr
- m = [d:d].Find("女", , , xlWhole).Row
- With Range("a2:e" & m - 1)
- .Sort Key1:=[e2]
- arr(1) = .Value
- End With
- With Range("a" & m & ":e" & [a65536].End(3).Row)
- .Sort Key1:=.Cells(1, 5)
- arr(2) = .Value
- End With
- Columns("e:j").ClearContents
- [g1:j1] = Array("宿舍号", "姓名", "学校", "性别")
- For l = 1 To 2
- For i = 1 To UBound(arr(l))
- d(i) = ""
- Next
- ReDim brr(1 To UBound(arr(l)), 1 To 4)
- r = 0
- For i = 1 To WorksheetFunction.RoundUp(UBound(arr(l)) / 6, 0) - 1
- For v = 1 To 6
- For ii = 1 To UBound(arr(l))
- If Len(arr(l)(ii, 3)) Then
- If Not ds.Exists(arr(l)(ii, 3)) Then
- r = r + 1
- ds(arr(l)(ii, 3)) = ""
- d.Remove ii
- brr(r, 1) = i
- For j = 2 To 4
- brr(r, j) = arr(l)(ii, j)
- Next
- arr(l)(ii, 3) = ""
- Exit For
- End If
- End If
- Next
- Next
- ds.RemoveAll
- Next
- k = d.keys
- m = i
- For i = 0 To d.Count - 1
- r = r + 1
- brr(r, 1) = m
- For j = 2 To 4
- brr(r, j) = arr(l)(k(i), j)
- Next
- Next
- [g65536].End(3).Offset(1).Resize(r, 4) = brr
- d.RemoveAll
- Next
- [a1].CurrentRegion.Sort Key1:=[a2], Header:=xlYes
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|