Random number from a list

acura123

Board Regular
Joined
Jul 13, 2003
Messages
87
I am trying to produce a random number from numbers not yet drawn from a pick 4 list of previously drawn numbers. So I want it to produce a random number between 0000 and 9999 and then check it agaist my list of previously drawn number in column c. If the random number picked is in the list then pick another number until it selects one that was not previously drawn. I figured out I could use Randbetween to generate my number but I am having trouble having that number check against the list and if it finds the number reselect another number until it get a number not previously drawn from the list. Try to make the solution as simple as possible.

Thanks In advance
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Try this in the code section of your sheet
driven by a command button

<code>
Dim RndTakenA(10000) As Boolean
' for random numbers taken Array
Dim RndA()
' to store your new random numbers assuming
' and then to write them out down column ??
Sub GetColC()
rr = 1 ' whatever row to start looking at Column C
While Cells(rr, 3) <> "" And rr < 20000
RndTakenA(Cells(rr, 3)) = True
' that number gone
rr = rr + 1
' go down rows untill blank
Wend
End Sub

Function FRnd(Lv As Integer, Hv As Integer) As Integer
' to pick random low value to high value
FRnd = Lv + Int((1 + Hv - Lv) * Rnd())
End Function
Sub GetRndA(Lv As Integer, Hv As Integer, Npick As Integer)
Dim ci As Integer, pi As Integer
RndANums = 0
While RndANums < Npick
pi = FRnd(Lv, Hv)
If Not RndTakenA(pi) Then ' if available
RndANums = RndANums + 1
RndA(RndANums) = pi
RndTakenA(pi) = True
End If
Wend
End Sub
Sub ListEm(colL As Integer, NumList As Integer)
Rof = 4
' what row to start at
For rr = 1 To NumList
Cells(rr + Rof, colL) = RndA(rr)
Next rr
End Sub
Private Sub CommandButton1_Click()
Dim NumRndWanted As Integer
NumRndWanted = 30
ReDim RndA(NumRndWanted)
GetColC ' get column c values
GetRndA 1, 9999, NumRndWanted
' low value, high value, number to pick
ListEm 6, NumRndWanted
'list them down the 6 th. column "F"
End Sub




</code>
 
Upvote 0
So I want it to produce a random number between 0000 and 9999 and then check it agaist my list of previously drawn number in column c. If the random number picked is in the list then pick another number until it selects one that was not previously drawn.
I think this would do that, assuming you'd like the bona fide number to be placed in cell A1.

Code:
Sub Test1()
Dim rng As Range, j%
j = Int(Rnd() * 10000)
Set rng = Columns(3).Find(j, lookat:=xlWhole)
While Not rng Is Nothing
j = Int(Rnd() * 10000)
Set rng = Columns(3).Find(j, lookat:=xlWhole)
Wend
Range("A1").Value = j
End Sub
 
Upvote 0
Thanks Tom
I put that in my good code section.

Could you add

How would you eliminate repeats if test1 was run a few hundred times.

Or how yould would add the selected rnd nums to column C

Or how you would add them to a new location

Or How you would add them to a named range.



My arrays etc are OK but I need to learn a lot ranges and named ranges.

Harry S.
 
Upvote 0
Hey Harry - - wow, a lot of questions there, I think they essentially boil down to a central question about how to add non-repeating random elements (numbers in our case) to a given range.

This is an example of populating range A1:D4 (16 cells total) with a number from 1 to 16 where no number is repeated:

Code:
Sub RandomSixteen() 
Application.ScreenUpdating = False
Dim x As Range, RanRng As Range, z As Range 
Dim i%, j% 
Set RanRng = Range("A1:D4") 
RanRng.ClearContents 
For Each x In RanRng 
j = Int(Rnd() * 16) + 1 
Set z = RanRng.Find(j, lookat:=xlWhole) 
While Not z Is Nothing 
j = Int(Rnd() * 16) + 1 
Set z = RanRng.Find(j, lookat:=xlWhole) 
Wend 
x = j 
Next x 
Application.ScreenUpdating = True
End Sub

From this point you can always increase the pool of acceptable elements. The 1 thru 16 pool requires the most "work" for the macro to execute because it results in a 1-to-1 fit of all possible elements whereas any pool greater than 16, example from 1 to 100 with this codeline instead
j = Int(Rnd() * 100) + 1
means it will run a bit faster due to the increased likelihood of an acceptable (non-repeated number) not being found in the While-Wend loop if the destination range stays at 16 cells.

And, alluding to your named range question, you can always name the range ahead of time and refer to that name in the code instead of the actual cell range address.

If this is not what you asked about, sorry, please repost but maybe it will provide enough of an example to apply to what you were thinking about.
 
Upvote 0
Thakks for that Tom,

Digested that OK to increase my ideas of ranges.

Further if there is a named range Fred as "b4:b34" and this is to be extended by putting value of a variable VVV in b35. How ??


Or if there is a named range Tom containing 12 elements not related to any sheet range ( If this is possible ?? ) how to add the value bbb as the 13 th.

Your help MUCH APPRECIATED



Harry S..
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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