vba - 使用 VBA 在单元格范围内生成真正的随机数

Question

我正在尝试随机分配大小为 20 X 25 的 B4:Z23 范围内的单元格，即总共 500 个单元格应该包含总共 500 个唯一值，随机值的范围是 1 到 500。因此每个数字只能在表中出现一次。
尝试使用下面的代码，但它会在某些单元格中生成重复项。

有人能帮我一下吗 ？

Option Explicit
Public Sub Random()

Dim RandomNumber As Integer
Dim i, j As Integer

For j = 2 To 26
Randomize
RandomNumber = Int((500 - 1 + 1) * Rnd + 1)
For i = 4 To 23
With Sheets("Game")
Randomize
RandomNumber = Int((500 - 1 + 1) * Rnd + 1)
Cells(i, j) = RandomNumber
End With
Next i
Next j
End Sub

Answer 1

因此，此代码将检查生成的每个随机数，看它是否与之前生成的任何值相同。如果是这样，它会生成一个新的随机数，直到它是唯一的。

Option Explicit
Public Sub Random()

    Dim RandomNumber As Integer
    Dim i, j, k, l As Integer

    Application.ScreenUpdating = False

    For j = 2 To 26
        For i = 4 To 26
            With Sheets("Game")
                Randomize
                RandomNumber = Int(500 * Rnd + 1)
                ' Search through all previous rows & columns (not including the current one)
                For k = 2 To j - 1
                    For l = 4 To i - 1
                        'If the current number is the same as a previous one choose a new one
                        Do While RandomNumber = Cells(l, k)
                            RandomNumber = Int(500 * Rnd + 1)
                        Loop
                        'Once the number is unique place it in the cell
                        Cells(i, j) = RandomNumber
                    Next l
                Next k
            End With
        Next i
    Next j


End Sub