"B. R.Ramachandran" wrote:
If he wants a static set, he could still follow the approach and do a
"copy"
on A2-A6 followed by "paste special'-- "values" on the same location.

Yes, I do the same for a quick-and-dirty implementation. The problem is:
it is difficult to generate a new set of random numbers quickly.

I prefer to put the =RAND() in some out-of-the-way column, and
copy-and-paste-special-value into the range depended on, B1:B15 in your
case.

Of course, the RAND column keeps generating new random values unnecessarily.
A small performance hit in this case. If it proves costly in larger
problems, obviously is it better to avoid them, either by pasting values
over as you suggest or by using a non-volatile macro.


----- original message -----

"B. R.Ramachandran" wrote in
message ...
I totally agree with you! Also, thanks for the =myRand() UDF.
I wasn't sure whether Paul wants a static set or a volatile regenerating
set
of non-repeating random numbers in A2-A6. Since =RANDBETWEEN() is also
volatile, I thought the approach is adequate.

If he wants a static set, he could still follow the approach and do a
"copy"
on A2-A6 followed by "paste special'-- "values" on the same location.

Kind regards,
Ramachandran

"JoeU2004" wrote:

"B. R.Ramachandran" wrote:
Every time you hit F9 button, you will have a new set of non-repeating
random numbers between 1 and15 in A2-A6.

.... And every time you edit any cell in the workbook :-(.

Presumably that's okay with Paul, since the same is true about
RANDBETWEEN.

But one way to avoid that "volatile" behavior is to replace =RAND() with
=myRand(), which is a UDF defined as follows:

Function myRand(Optional arg as Range) as Double
Static first as Integer
If first = 0 Then Randomize: first = 1
myRand = Rnd()
End Function

If you pass a cell or cell range to myRand, it will recalculate whenever
any
cell in the range is edited. Otherwise, myRand is recalculated only when
the worksheet is recalculated, e.g. ctrl+alt+F9.


----- original message -----

"B. R.Ramachandran" wrote in
message ...
Hi,

In a helper column, say B1 to B15, enter the formula =RAND(). This
will
create 15 random numbers.

Now, in A2, enter the formula, =RANK(B1,$B$1:$B$15,1)
and drag (autofill) the formula down to A6.

Every time you hit F9 button, you will have a new set of non-repeating
random numbers between 1 and15 in A2-A6.

Regards,
B. R. Ramachandran




"Paul" wrote:

I'm using the formula =RANDBETWEEN(1,15)
to generate random numbers in cells A2 through A6.
I don't want any of those random numbers to be repeated.
Any suggestions on how to do this?
Thanks for your help.