Generating Unique Random Numbers in VBA Excel

Problem: Generate a list of unique random integers between two bounds in VBA and Excel

Solution: I started with the code from here. The original code output the random numbers as a string residing within the same cell. Needed each random number to appear in its own cell. The context was to randomize seating for a poker game, hence the variable names players

I pull the input data from the excel cells directly so adjust your code to match the cells you want to input:

Top - upper bound

Bottom - lower bound

Players - cell range of players (The range of cells next to the list of player where each player name is located in a column of cells)

Execute code on button click

Sub Seating()

Dim iArr As Variant

Dim i As Integer

Dim r As Integer

Dim Top As Integer

Dim Bottom As Integer

Dim temp As Integer

Dim Players As Range, Cell As Object

Set Players = Range("W10:W25")

Top = [V7]

Bottom = 1

Application.Volatile

ReDim iArr(Bottom To Top)

For i = Bottom To Top

iArr(i) = i

Next i

For i = Top To Bottom + 1 Step -1

r = Int(Rnd() * (i - Bottom + 1)) + Bottom

temp = iArr(r)

iArr(r) = iArr(i)

iArr(i) = temp

Next i

j = 1

For Each Cell In Players

Cell = iArr(j)

j = j + 1

If j > Top Then

Exit For

End If

Next

End Sub