excel - 我想要 50 个 5 列 10 行的随机数组的数据，具有唯一且没有重复值。这里为什么显示重复值？

Question

Sub Button3_Click()
Dim FillRange As Range, c As Range

Set FillRange = Range("A1:A10")

For x = 0 To 4

Set FillRange = Range("A1:A10").Offset(, x)

For Each c In FillRange

    Do

        c.Value = Int((50 - 1 + 1) * Rnd + 1)

    Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2

Next

Next

End Sub

Answer 1

“随机排序”方法:

Sub tester()
    
    Dim arr, newArr
    
    arr = Application.Transpose(Application.Evaluate("ROW(1:50)")) 'array 1 to 50
    
    Debug.Print "Original:" & vbLf & Join(arr, vbLf)
    
    SortSpecial arr, "RandomVal"

    Debug.Print "Rearranged:" & vbLf & Join(arr, vbLf)

    'put arr into the worksheet...

End Sub

'Sorts an array using some specific translation defined in `func`
Sub SortSpecial(list, func As String)
    Dim First As Long, Last As Long, i As Long, j As Long, tmp, arrComp()
    First = LBound(list)
    Last = UBound(list)
    'fill the "compare array...
    ReDim arrComp(First To Last)
    For i = First To Last
        arrComp(i) = Application.Run(func, list(i))
    Next i
    'now sort by comparing on `arrComp` not `list`
    For i = First To Last - 1
        For j = i + 1 To Last
            If arrComp(i) > arrComp(j) Then
                tmp = arrComp(j)          'swap positions in the "comparison" array
                arrComp(j) = arrComp(i)
                arrComp(i) = tmp
                tmp = list(j)             '...and in the original array
                list(j) = list(i)
                list(i) = tmp
            End If
        Next j
    Next i
End Sub

Function RandomVal(v)
    RandomVal = Rnd()
End Function