exclude a number from randbetween function
 

I am trying to get 4 different random numbers, all between 1 and 20, without
duplicating the numbers. Can anyone advise me on how to do this. I am
curently using randbetween function, 4 times, but I get the same number twice
every now and again

exclude a number from randbetween function
 

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'Generates five random numbers between 1 and 20
'with no duplicates. Concept stolen from Tom Ogilvy.
'Jim Cone - San Francisco, USA
Sub GetThem()
Dim arrCheck(1 To 20) As Long
Dim arrList(1 To 5) As Long
Dim j As Long
Dim N As Long
Const LNG_PLUG As Long = 999

j = 1
Do While j < 6
'Get a random number
Randomize
N = Int(Rnd * 20 + 1)
'If number unique then add to arrList.
If arrCheck(N) < LNG_PLUG Then
arrList(j) = N
arrCheck(N) = LNG_PLUG
j = j + 1
End If
Loop

Range("B5:F5").Value = arrList()
End Sub
'-----------------



"Koreknots"

wrote in message
I am trying to get 4 different random numbers, all between 1 and 20, without
duplicating the numbers. Can anyone advise me on how to do this. I am
curently using randbetween function, 4 times, but I get the same number twice
every now and again

exclude a number from randbetween function
 

A formula method...

In A1 =RAND() copied down to A20

in B1 copied down to B4

=RANK(A1,A$1:A$20)

These are your 4 random numbers - hit F9 to regenerate - hide column
if require

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56447

exclude a number from randbetween function
 

Thanks, works a treat.

"daddylonglegs" wrote:


A formula method...

In A1 =RAND() copied down to A20

in B1 copied down to B4

=RANK(A1,A$1:A$20)

These are your 4 random numbers - hit F9 to regenerate - hide column A
if required


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564478