How would i set up a macro to generate six random numbers
in consecutive cells between 1 and 50. There cannot be a
any duplicate numbers though?

Thanks in advance for your help.

Ed

The usual way to avoid duplicates is to use two columns: one column
containing the numbers 1 ... 50 and the second column containing random
numbers. Sort both columns based on the second column, and take the
first six numbers from the first column.

Jerry

ed wrote:

How would i set up a macro to generate six random numbers
in consecutive cells between 1 and 50. There cannot be a
any duplicate numbers though?

Thanks in advance for your help.

Ed

Ed,

Here's one way.

Goto ToolsOptions and on the Calculation tab click the Iteration checkbox
(this will suppress circular reference messages)
Put this formula in A1 and copy down to A50
=IF(OR(A1=0,B11),INT(RAND()*100),A1)
Put this formula in B1 and copy down to B50
=COUNTIF(A:A,A1)

Change the RAND function to your max values, but make sure it is large
enough to allow unique numbers (90 was the lowest I could get)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ed" wrote in message
...
How would i set up a macro to generate six random numbers
in consecutive cells between 1 and 50. There cannot be a
any duplicate numbers though?

Thanks in advance for your help.

Ed

You could also try this fairly simple method:-

In A1 type =RAND()
Copy this to A2:A50

In B1 type =RANK(A1,a$1:a$50)
Copy this to B2:B6

You will be *ranking* each of the random numbers in A1 to A6 as they appear
within the group A1 to A50 and showing this ranking as an integer in B1:B6

In theory this method could yield duplicates but probably not - given the
small quantity of numbers involved i.e. 6 from 50.

HTH
Dave



"ed" wrote in message
...
How would i set up a macro to generate six random numbers
in consecutive cells between 1 and 50. There cannot be a
any duplicate numbers though?

Thanks in advance for your help.

Ed

In theory, it won't produce duplicates since the period of the RAND
algorithm is well in excess of 50 and numbers do not repeat within the
period.

--
Regards,
Tom Ogilvy

"Dave B" wrote in message
...
You could also try this fairly simple method:-

In A1 type =RAND()
Copy this to A2:A50

In B1 type =RANK(A1,a$1:a$50)
Copy this to B2:B6

You will be *ranking* each of the random numbers in A1 to A6 as they
appear
within the group A1 to A50 and showing this ranking as an integer in B1:B6

In theory this method could yield duplicates but probably not - given the
small quantity of numbers involved i.e. 6 from 50.

HTH
Dave



"ed" wrote in message
...
How would i set up a macro to generate six random numbers
in consecutive cells between 1 and 50. There cannot be a
any duplicate numbers though?

Thanks in advance for your help.

Ed