random numbers

abz2max

New Member
Joined
Apr 16, 2002
Messages
19
I need to create a list of random numbers, that do not repeat each other. The range of numbers i will need will be 1 to n. n = any number>1. how can i create a macro to do this.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
God, I really don't want to turn this into a huge discussion like those other two, but this has been nagging at me:

Dave H suggested the code for picking lottery numbers:

<pre>
Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer)
Dim iNum As String
Dim strNum As String
Dim i As Integer

Application.Volatile
iNum = Int((Top - Bottom + 1) * Rnd + Bottom)

For i = 1 To Amount
strNum = Trim(strNum & " " & iNum)
Do Until InStr(1, strNum, iNum) = 0
iNum = Int((Top - Bottom + 1) * Rnd + Bottom)
Loop
Next i

RandLotto= strNum

End Function</pre>

I thought about this one day and realised that there is a flaw in this technique. This technique is biased against single digit numbers. That is to say, if "49" came up as the first pick in this code, the numbers "4" and "9" could not be individually picked as a single digit number. This is due to the "instr" check that Dave uses.

Sorry if this turns into a huge discussion again, but this seemed like a good opportunity to address this without revisiting the old threads.
 
Upvote 0
Hi Mark,

Excellent!! Nice catch. Will return unique numbers but is biased.

Thanks for the post.

Bye,
Jay

P.S. FYI, the following post is, in my opinion, the most amazing of this entire Excel/RNG topic.

I have specified the link to Harlan Grove's post, but for those interested, please read the whole thread, including Harlan's discussion with Tushar Mehta afterward.

http://groups.google.com/groups?hl=...600@bgtnsc06-news.ops.worldnet.att.net&rnum=5
 
Upvote 0
Good thread. Interesting to me since I have a vague grasp of bias and such statistical "nonsense". (I'm an engineer, statistics have to be treated carefully in my field)

The one thing that interests me in Harlan's code is that ahe doesn't use "Randomize" to initialise VB's RNG. Now, I ran into a problem with this myself. I made a little High/Low guessing game in Excel. (kind of like "Play Your Cards Right" and whatever the name of the US version was might have been "Jokers Wild") Anyway, without initialising the RNG I found that if I restarted to play my game again, I got the same "random" numbers each time.

I think using "Randomize" changes the seed. Again, I don't want to turn this into a hee-yuge discussion, but I know that it's going to head that way. :biggrin:
 
Upvote 0
Hi,

I just posted to .worksheetfunctions asking this. I should get a few responses soon, and Harlan will respond if he sees the post.

Bye,
Jay
 
Upvote 0
Hi,

Here is Harlan's response:

Jay Petrulis <jpetrulis@lycosmail.com> wrote...
....
>I have one question, for Harlan in particular, about his
>code. Could it be improved (made "more random") if
>
>Randomize
>
>is used to initialize the RNG and was included before the
>VBA Rnd function is invoked, so that the "seed" is
>reshuffled? Was/is it unnecessary in this instance?
....

It never hurts. Just because I didn't include it doesn't mean I had a reason
not to include it other than simply forgetting to do so.

Note: all Rnd calls in all VBA procedures use the same pseudorandom number
generator, so it's only necessary to call Randomize once in any Excel
session (though I guess it might be necessary to call it in each additional
Excel application instance if using multiple Excel sessions - dunno whether
the state variables are stored in shared DLL memory space or in each
application instance's own memory space). I call Randomize in Worksheet_Open
in my Personal.xls file, so I never think of adding it elsewhere.

Didn't know that one session thing. Calling Randomize when Excel is opened is pretty cool. Adding Randomize to his code would make his routine even better, for those who haven't already initialized the RNG with it.

Bye,
Jay
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top