|
楼主 |
发表于 2014-3-6 18:10
|
显示全部楼层
香川群子 发表于 2014-3-6 09:36
如果输出列要和源数据列不同,这样肯定会麻烦一点。
第一要重新定义数组、第二每次存放也要重新转换计 ...
下标越界,不知问题出在哪?请再帮修改一下,谢谢了!- Sub RctDataRnd() '输出列数修改,输出的列数可以用 inputbox 来选择,假如用 inputbox 来选择输出,可以是1 列,5列,......用户可以根据需要决定,人性化。
- Dim rw&, cl&, i&, j&, ii&, jj&, m&, n&, r&, t, k&, YY, crr
-
- arr = [a1].CurrentRegion.Offset(1)
- m = Application.CountA([a1].CurrentRegion.Offset(1))
- rw = UBound(arr) - 1: cl = UBound(arr, 2)
- [a1].Offset(1, cl + 1).CurrentRegion = ""
- 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
-
- 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 n, 1 To 1)
- On Error Resume Next
- If n < 65536 Then
- YY = Val(InputBox("请输入要生成的列数?", ""))
- r = Int(n / YY) + 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
复制代码 |
|