# Random number gererator

Discussion in 'Business Applications' started by TBaker14, Jan 31, 2013.

Not open for further replies.

Joined:
Feb 11, 2010
Messages:
132
I'd like to know if there is a formula to randomly generate numbers between 0 - 9 over 10 cells...but with no duplicates.

Thoughts?

TBaker14

2. ### BigTobster

Joined:
Feb 26, 2011
Messages:
173

Joined:
Mar 27, 2008
Messages:
6,576
First Name:
Hans
He meant RAND()

4. ### JohnWilson

Joined:
Nov 22, 2007
Messages:
262
I don't think he does!

Joined:
Mar 27, 2008
Messages:
6,576
First Name:
Hans
RANK() is to set the ranking of series of numbers, RAND() is the random generator, maybe the link is Okay, I did not check that but RAND() is the number generator.

6. ### JohnWilson

Joined:
Nov 22, 2007
Messages:
262
Tushar was using the RANK Function. I think I'd do this

Example call NONREPEATRAND 1,13,"C10"

Function NONREPEATRAND(lngLower As Long, lngUpper As Long, STARTCELL As String)
Dim ocol As New Collection
Dim i As Integer
For i = lngLower To lngUpper
Next
Randomize
For c = 1 To lngUpper - lngLower
i = Int(Rnd * ocol.Count) + 1
Sheet1.Range(STARTCELL).Offset(c).Value = ocol(i)
ocol.Remove (i)
Next c
End Function

I'm sure there's an easier way in Excel but then I'm a PowerPoint coder!

As Seen On