Random number generation

[Neil Higgins]

How do I generate random numbers in a column of cells but avoid duplications.
i.e. Every cell contains a unique whole number from a specific range.

 

[JW]

have a look here:
http://www.ozgrid.com/VBA/RandomNumbers.htm

 

[NateBuckley]

Hello I just made this as I was also intruiged on how to do this, not the
best way to do it no doubt but uses a bucket search and displays them in
order.

Sub randomCol()
Dim bucket() As Boolean
Dim whichCol As String
Dim rndArray() As Integer
Dim howManyRows As Integer
Dim j As Integer
Dim i As Integer

rollSize = 100 'Go up to
howManyRows = 10 'How many different numbers you want along with how
many rows to go down
ReDim bucket(1 To rollSize)

For i = 1 To howManyRows
If bucket(reroll(rollSize)) = False Then
bucket(reroll(rollSize)) = True
Else
i = i - 1
End If
Next i

j = 1
For i = 1 To UBound(bucket)
If bucket(i) = True Then
Sheets("Sheet1").Cells(j, 1).Value = i
j = j + 1
End If
Next i
End Sub

Private Function reroll(rollSize) As Integer
Randomize
reroll = Int((Rnd * rollSize) + 1)
End Function


One of the problems is you can end up with a huge ass array if you wish lots
of random numbers.

Hope this helps!

 

[Jim Cone]

Or maybe the free Special Randoms workbook will do what you want.
Download from the Products page at... http://www.realezsites.com/bus/primitivesoftware
Numbers generated in a new workbook - then copy | paste where you want.
--
Jim Cone
Portland, Oregon USA

(Excel Add-ins / Excel Programming)


"Neil Higgins"
<Neil (e-mail address removed)>
wrote in message
How do I generate random numbers in a column of cells but avoid duplications.
i.e. Every cell contains a unique whole number from a specific range.

 

[Lynda]

How do I generate random numbers in a column of cells but avoid duplications.
i.e. Every cell contains a unique whole number from a specific range.

Chip Pearson has a good one here:
http://www.cpearson.com/Excel/randomNumbers.aspx

The "no duplicates" version is about midway down the page...