Thursday, March 10, 2011

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

1 comment:

Hylic said...

thanks for sharing.