|
本帖最后由 lijian8003 于 2015-11-17 11:32 编辑
- Sub 随机取数()
- Sheets("1").Select
- Dim a(), b(100)
- Dim s As New Collection
- a = Range([a2], [a2].End(4))
- For i = 1 To UBound(a)
- s.Add a(i, 1)
- Next
- Randomize
- For j = 1 To 100
- k = Int(Rnd * s.Count + 1)
- b(j) = s(k)
- s.Remove (k)
- Next
- [hc1].Resize(101, 1) = Application.WorksheetFunction.Transpose(b)
- End Sub
复制代码 上述代码是在工作表“1”中,将A列中随机提取100个单元格的数据,写入HC列
现在欲扩展运用:
1、在工作表“1”中,有A-HB共210列数据,欲分别在A-HB列中随机提取100个单元格的数据,依次写入HC HD HE....PD列
2、这样的工作表共有42个,分别是工作表“1”、工作表“2”、工作表“3”...工作表“42”
在上述代码中,如何添加如上所示的二个循环?
'主程序
Sub test()
Dim A, B, C, i, j, k
For k = 1 To 2 '2改为42
Sheets(k).Range("d:xfd").ClearContents 'd:xfd 改为 hc:xfd
A = Sheets(k).UsedRange.Value
ReDim B(1 To UBound(A))
For j = 1 To UBound(A, 2)
For i = 1 To UBound(A)
B(i) = A(i, j)
Next i
Call getRnd(A, B, C, k, j)
Next j
Next k
End Sub
'从某列中,随机取出z个数
Sub getRnd(A, B, C, k, j)
Dim x, y, z, i, t
x = 1
y = UBound(A)
z = 100
ReDim C(1 To z, 1 To 1)
For i = x To z + x - 1
t = Int((y - i + 1) * Rnd) + i
C(i - x + 1, 1) = B(t)
B(t) = B(i)
Next
Sheets(k).Cells(2, j + UBound(A, 2)).Resize(UBound(C)) = C
End Sub
随机取数2.rar
(126.96 KB, 下载次数: 8)
|
|