|
- Sub RctDataRnd() '输出列数修改,输出的列数可以用 inputbox 来选择,假如用 inputbox 来选择输出,可以是1 列,5列,......用户可以根据需要决定,人性化。
- Dim rw&, cl&, i&, j&, ii&, jj&, m&, n&, r&, t, k&, YY, crr, rng As Range, n2%
- Set rng = Application.InputBox("请选择源区域", "温馨提示如A2:D21", , , , , , 8)
- If rng Is Nothing Then Exit Sub
- arr = rng
- m = Application.CountA(rng)
- rw = UBound(arr) - 1: cl = UBound(arr, 2)
- n = Int(Val(InputBox("How many ?" & vbCr & " [1 to " & m & "]", "Get Rand", 12))) '抽取个数n
- If n <= 0 Or n > m Then MsgBox "HeadCounts is not correct.": Exit Sub
- n2 = n
- Randomize
- For i = 1 To rw
- For j = 1 To cl
- Do
- r = Int(Rnd() * ((rw - i + 1) * cl - j + 1)) + (i - 1) * cl + j - 1
- ii = Int(r / cl) + 1: jj = r Mod cl + 1
- t = arr(ii, jj)
- If t <> "" Then
- arr(ii, jj) = arr(i, j): arr(i, j) = t
- n = n - 1: If n = 0 Then Exit For Else Exit Do
- End If
- Loop
- Next
- If n = 0 Then
- For j = j + 1 To cl
- arr(i, j) = ""
- Next
- Exit For
- End If
- Next
- '[a1].Offset(1, cl + 1).Resize(i, cl) = arr '输出列数?????
- ReDim jg$(1 To n2, 1 To 1)
- On Error Resume Next
- If n2 < 65536 Then
- YY = Val(InputBox("请输入要生成的列数?", ""))
- r = n2 \ YY
- If r * YY < n2 Then r = r + 1
- ReDim crr(1 To r, 1 To YY)
- For i = 1 To r
- For j = 1 To YY
- crr(i, j) = jg((i - 1) * YY + j, 1)
- Next j
- Next i
- Set rng = Application.InputBox("请选择存放区域(单个单元格即可)", "VBA", , , , , , 8)
- rng.CurrentRegion.ClearContents
- rng.Resize(r, YY) = crr
- End If
- End Sub
复制代码 后面的jg数组赋值的问题你自己想下,是怎么回事。
|
|