RANDBETWEEN

  • Thread starter Thread starter Jerm
  • Start date Start date
J

Jerm

I am trying to return a Random number between 1:32 in 20 different cells
without repeating. Anyone?
 
I assume you want a random whole number.

But RAND() in A2:A34, then do RANK(A2,A2:A34) and grab the first 20.
 
Hi,

Right click your sheet tab, view code and paste the code below in on the
right and run it

Sub sonic()
Dim FillRange As Range
Set FillRange = Range("A1:A20")' Change to suit
For Each c In FillRange
Do
c.Value = Int((32 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

Mike
 
Almost!
I need a random number from 1-32, then in the next cell, I need a random
number between 1-32 but not the first cell nuumber and so on. This is going
to need to work for 1-96 and 1-64. I'll try your first suggestion further.
Thanks,
 
That worked, but how do I put in a new one for the next column? The next one
needs to be for 1-100.
Thanks,
 
Maybe something like this...

Sub sonic1to32()
Dim FillRange As Range
Set FillRange = Range("A1:A20")
For Each c In FillRange
Do
c.Value = Int((32 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
sonic1to64
End Sub

Sub sonic1to64()
Dim FillRange As Range
Set FillRange = Range("B1:B20")
For Each c In FillRange
Do
c.Value = Int((64 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
sonic1to96
End Sub

Sub sonic1to96()
Dim FillRange As Range
Set FillRange = Range("C1:C20")
For Each c In FillRange
Do
c.Value = Int((96 * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

HTH
Regards,
Howard
 
Thanks for the updates to my macro but I'm sure with a little ingenuity and
an input box you could have a single macro do any number range you want :)

Mike
 
If you like RANDBETWEEN here is another approach

Enter RANDBETWEEN in as many cells as you want, say A1:A64 then in B1 enter
=SUMPRODUCT(COUNTIF(A1:A64,A1:A64))=COUNT(A1:A64)

Now either press F9 until B1 = FALSE or run the following macro:

Sub myRand()
Do Until [B1]= TRUE
Activesheet.Calculate
Loop
End Sub

Of course, since RANDBETWEEN is volatile you need to copy and paste the set
somewhere else as values.
 
I'm curious to know if you tested that and, if so, how long did it take to
get 64 unique random numbers?

--
Biff
Microsoft Excel MVP


Shane Devenshire said:
If you like RANDBETWEEN here is another approach

Enter RANDBETWEEN in as many cells as you want, say A1:A64 then in B1
enter
=SUMPRODUCT(COUNTIF(A1:A64,A1:A64))=COUNT(A1:A64)

Now either press F9 until B1 = FALSE or run the following macro:

Sub myRand()
Do Until [B1]= TRUE
Activesheet.Calculate
Loop
End Sub

Of course, since RANDBETWEEN is volatile you need to copy and paste the
set
somewhere else as values.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Jerm said:
I am trying to return a Random number between 1:32 in 20 different cells
without repeating. Anyone?
Click to expand...
Click to expand...
 
Actually I did test it, but keep in mind the key here is what range you enter
in the RANDBETWEEN function, if you choose 1, and 64 bad news, if you choose
0, and 1000000, its done in a second.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


T. Valko said:
I'm curious to know if you tested that and, if so, how long did it take to
get 64 unique random numbers?

--
Biff
Microsoft Excel MVP


Shane Devenshire said:
If you like RANDBETWEEN here is another approach

Enter RANDBETWEEN in as many cells as you want, say A1:A64 then in B1
enter
=SUMPRODUCT(COUNTIF(A1:A64,A1:A64))=COUNT(A1:A64)

Now either press F9 until B1 = FALSE or run the following macro:

Sub myRand()
Do Until [B1]= TRUE
Activesheet.Calculate
Loop
End Sub

Of course, since RANDBETWEEN is volatile you need to copy and paste the
set
somewhere else as values.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


Jerm said:
I am trying to return a Random number between 1:32 in 20 different cells
without repeating. Anyone?
Click to expand...
Click to expand...
Click to expand...
 
Sean Timmons said:
I assume you want a random whole number.
But RAND() in A2:A34, then do RANK(A2,A2:A34) and grab the first 20.
Click to expand...

Almost right. Not sure why you start in row 2 and why you use 33 instead of
32 cells.

If "Jerm" wants a column of 20 numbers, then put RAND() into A1:A32, and put
the following into B1 and copy down through B20:

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

Note that A1:A32 and B1:B20 each can be relocated anywhere.


----- original message -----
 
if you choose 1, and 64 bad news

I tried 1 - 3 and it only took 4 iterations.

I then tried 1 - 10 and gave up after an hour!

--
Biff
Microsoft Excel MVP


Shane Devenshire said:
Actually I did test it, but keep in mind the key here is what range you
enter
in the RANDBETWEEN function, if you choose 1, and 64 bad news, if you
choose
0, and 1000000, its done in a second.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


T. Valko said:
I'm curious to know if you tested that and, if so, how long did it take
to
get 64 unique random numbers?

--
Biff
Microsoft Excel MVP


Shane Devenshire said:
If you like RANDBETWEEN here is another approach

Enter RANDBETWEEN in as many cells as you want, say A1:A64 then in B1
enter
=SUMPRODUCT(COUNTIF(A1:A64,A1:A64))=COUNT(A1:A64)

Now either press F9 until B1 = FALSE or run the following macro:

Sub myRand()
Do Until [B1]= TRUE
Activesheet.Calculate
Loop
End Sub

Of course, since RANDBETWEEN is volatile you need to copy and paste the
set
somewhere else as values.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

I am trying to return a Random number between 1:32 in 20 different
cells
without repeating. Anyone?
Click to expand...
Click to expand...
Click to expand...
 
Jerm said:
That worked, but how do I put in a new one for the next column?
The next one needs to be for 1-100.
Click to expand...

If you want a column of numbers, put =RAND() into A1:A100.

If you want 20 numbers between 1 and 32, put the following into B1 and copy
down through B20:

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

If you also want 30 numbers between 1 and 100, put the following into C1 and
copy down through C30:

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

Note that A1:A100, B1:B20 and C1:C30 each can be relocated anywhere.

However, note that since RAND() is volatile, your numbers will change every
time you modify the workbook :-(.

You can avoid that by copying A1:A100 and doing paste-special-value back to
A1:A100 or some other range. Alternatively, replace =RAND() with =myrand(),
and add the following UDF:

Function myrand(Optional cell as Range) as double
myrand = Rnd()
End Function

If you use =myrand(), you can regenerate random numbers by pressing
ctrl+alt+F9. If you use =myrand(X1), you can regenerate random numbers by
modifying X1.


If you prefer a macro approach, try the following....

Sub doit()
Const targetRange As String = "D1:D20"
Const high As Integer = 100
Const low As Integer = 1
Dim n As Integer, i As Integer, cell As Range
n = high - low + 1
ReDim numbers(n)
For i = 1 To n: numbers(i) = i: Next i
For Each cell In Range(targetRange)
i = Int(1 + n * Rnd())
cell = numbers(i)
If i < n Then numbers(i) = numbers(n)
n = n - 1
Next cell
End Sub

Change targetRange and high as needed. I also give you the option of
changing low.

The macro generates as many unique numbers between low and high as there are
cells in targetRange.

Note: If there are more cells in targetRange than there are numbers between
low and high, the macro will terminate with an error.


----- original message -----
 
Errata....
I also give you the option of changing low.
Click to expand...

That was a half-baked after-thought. To fully implement, one line of the
macro needs to be changed, to wit:

For i = 1 To n: numbers(i) = low + i - 1: Next i

The complete corrected macro is:

Sub doit()
Const targetRange As String = "D1:D20"
Const high As Integer = 100
Const low As Integer = 1
Dim n As Integer, i As Integer, cell As Range
n = high - low + 1
ReDim numbers(n)
For i = 1 To n: numbers(i) = low + i - 1: Next i
For Each cell In Range(targetRange)
i = Int(1 + n * Rnd())
cell = numbers(i)
If i < n Then numbers(i) = numbers(n)
n = n - 1
Next cell
End Sub


----- original message -----
 
I have at http://www.cpearson.com/Excel/RandomNumbers.aspx a VBA
function called UniqueRandomLongs that will return N unique integers
between a specified min and max.

Copy the function UniqueRandomLongs from the web site, or download the
zip file and import the zipped bas file to your project, and then call
the function from an array of cells. For example, to get 20 unique
numbers between 1 and 32, select the cells to which you want the
numbers to returned and type one of following formulas and press CTRL
SHIFT ENTER rather than just ENTER:

If your selected range is one column spanning several rows, use

=TRANSPOSE(UniqueRandomLogns(1,32,20))

If your selected range is on one row spanning multiple columns, use

=UniqueRandomLongs(1,32,20)

Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top