# Random number from a list

#### acura123

##### Board Regular
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### HarryS

##### Board Regular
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>

#### Tom Urtis

##### MrExcel MVP
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``````

#### acura123

##### Board Regular
Works GREAT! Thanks For all your Help!

#### HarryS

##### Board Regular
Thanks Tom
I put that in my good code section.

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.

#### Tom Urtis

##### MrExcel MVP
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.

#### HarryS

##### Board Regular
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.

Harry S..

Replies
26
Views
657
Replies
6
Views
999
Replies
4
Views
248
Replies
6
Views
125
Replies
4
Views
358

### Forum statistics

1,191,287
Messages
5,985,756
Members
439,979
Latest member
alekun86 ### 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.

### Which adblocker are you using?    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

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