|
从sheet2的a3行里随机取2组2个数一组,2组3个数一组,(同一组里的数互不重复)
分别放进sheet1的 b17,c17(一组),b18,c18(一组),b19,c19,d19(一组),b20,c20,d20(一组)
要求 必不与sheet2 a2 行里的数
a2和a3行单元格的个数不一定
怎么代码格式没了? - Sub 随机()
- Dim xrng As Range
- Dim datarange As Integer
- Dim brr(1 To 4, 1 To 3)
- Dim rng As Integer
- With Sheet2
- Set xrng = .Range(.[a2], .[a2].End(xlToRight)) '不参与随机的数字
- arr = .Range(.[a3], .[a3].End(xlToRight)) '所有数字
- ReDim crr(1 To UBound(arr, 2)) '所有数字中去掉不参与随机的数字
- For i = 1 To UBound(arr, 2)
- If Application.WorksheetFunction.CountIf(xrng, arr(1, i)) = 0 Then '去掉不参与随机的数字
- n = n + 1
- crr(n) = arr(1, i)
- End If
- Next
- End With
-
- For k = 1 To 4
- drr = crr
- p = n
- q = IIf(k <= 2, 2, 3)
- For i = 1 To q
- rng = Int((p * Rnd) + 1)
- brr(k, i) = drr(rng)
- drr(rng) = drr(p)
- p = p - 1
- Next
- Next
- Sheet1.Range("b15").Resize(4, 3) = brr
- End Sub
复制代码
|
|