|
本帖最后由 爱疯 于 2012-2-6 23:31 编辑
思路:
先为每个考号添加一个各不相同的随机数字(此表为滚动存入的),再把考生按这些随机数按升序的顺序排列
排列后 生成新的序号(1到有数据的行 结束)(补充:编排结果输出到 随机编号)
代码:
Private Sub 随机编号()Dim arr1(), i As Integer, i1 As Integer, n As Integer, j As Integer, y As Integer, t As Boolean, temp1i = Sheets("提取库").Range("a65536").End(xlUp).RowIf i < 2 Then Exit Subarr1 = Sheets("提取库").Range("a2:e" & i).Valuei = 1Randomize Do While i <= UBound(arr1) Do n = Int((UBound(arr1) * Rnd) + 1) Loop While n < i For j = 1 To 5 temp1 = arr1(i, j) arr1(i, j) = arr1(n, j) arr1(n, j) = temp1 Next arr1(i, 1) = i i = i + 1 LoopFor i = 1 To 2 For j = 1 To 288 n = (i - 1) * 288 + j If n > UBound(arr1) Then Exit For Exit For End If arr1(n, 1) = j Next jNext iSheets("随机编号").Range("a3").Resize(UBound(arr1), 5) = arr1End Sub
如果要在VBA内存数组中进行随机排序,那么代码如下: - Sub Macro2()
- With Sheets(1)
- n = .[a65536].End(3).Row - 2
- arr = .[a3].Resize(n, 8)
- End With
- x = WorksheetFunction.Transpose(Application.Index(arr, , 1))
- '获取列序号(或任何作为随机排序依据的列的内容)压入一维数组用来随机
- Call GetRnd(x, n) '调用经典随机洗牌过程 进行高效不重复随机排序
-
- brr = arr
- For i = 1 To n
- brr(i, 1) = i 'brr数组第1列生成新的序号
- For j = 2 To 8
- brr(i, j) = arr(x(i), j) '把arr数组内容按新的随机排序值重新整理数据到brr数组
- Next
- Next
-
- Sheets(2).[a3].Resize(n, 8) = brr '在表2输出结果
- Sheets(2).Activate
-
- End Sub
- '经典随机洗牌过程代码,以后可以在别的代码中使用
- '因此,我把它单独列出,而不是放在过程中。
- Sub GetRnd(arr, n)
- Randomize
- For i = 1 To n
- r = Int(Rnd() * (n - i + 1)) + i
- t = arr(r): arr(r) = arr(i): arr(i) = t
- Next
- End Sub
复制代码
|
|