random number selection

G

Guest

Guest
Hello guys! I have a list numbers (#1-#30), and I would like to see if I can write some VBA so that I can push a button and then excel will automatically randomly select 4 number from that range. Maybe the outcome would be in a set of cells on the workbook listing the numbers, or it would simply highlight four cells containing the numbers that were randomly selected. Basically, I want to see if I can create something to randomly select 4 numbers from this range over and over....thanks guys.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this......

Sub random()
Dim r(4)
Randomize
For x = 1 To 4
10 rnum = Int((30 * Rnd) + 1)
If c = rnum Then GoTo 10
r(x) = Cells(rnum, 1)
c = rnum
Cells(x, 2) = r(x)
Next x
End Sub

Hope this helps.
 
Upvote 0
sometimes the random numbers bring back two of the same number (ie- 22 is lsted twice) anyway to prevent this?

Thanks!
 
Upvote 0
On 2002-03-04 06:29, Anonymous wrote:
Hello guys! I have a list numbers (#1-#30), and I would like to see if I can write some VBA so that I can push a button and then excel will automatically randomly select 4 number from that range. Maybe the outcome would be in a set of cells on the workbook listing the numbers, or it would simply highlight four cells containing the numbers that were randomly selected. Basically, I want to see if I can create something to randomly select 4 numbers from this range over and over....thanks guys.


Try this. It will enter the four numbers in A1:A4 :-

Dim rng As Range, cell As Range
Set rng = [A1:A4]
With rng
repeat:
.FormulaR1C1 = "=RANDBETWEEN(1,30)"
.Value = .Value
For Each cell In rng
If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then GoTo repeat
Next
End With
This message was edited by Autolycus on 2002-03-04 15:47
 
Upvote 0
Sorry my bad I forgot a small extra bit :)

try this updated macro this will give you ALL the 30 numbers in a random order, to go back to just 4 change the 30 in the "for x=1 to 30" line, to a 4, the dims can remain as is

Sub random()
Dim r(30), c(30)
Randomize
For x = 1 To 30
10 rnum = Int((30 * Rnd) + 1)
If c(rnum) = rnum Then GoTo 10
r(x) = Cells(rnum, 1)
c(rnum) = rnum
Cells(x, 2) = r(x)
Next x
End Sub
 
Upvote 0
On 2002-03-04 15:44, Autolycus wrote:
On 2002-03-04 06:29, Anonymous wrote:
Hello guys! I have a list numbers (#1-#30), and I would like to see if I can write some VBA so that I can push a button and then excel will automatically randomly select 4 number from that range. Maybe the outcome would be in a set of cells on the workbook listing the numbers, or it would simply highlight four cells containing the numbers that were randomly selected. Basically, I want to see if I can create something to randomly select 4 numbers from this range over and over....thanks guys.


Try this. It will enter the four numbers in A1:A4 :-

Dim rng As Range, cell As Range
Set rng = [A1:A4]
With rng
repeat:
.FormulaR1C1 = "=RANDBETWEEN(1,30)"
.Value = .Value
For Each cell In rng
If Application.WorksheetFunction.CountIf(rng, cell.Value) > 1 Then GoTo repeat
Next
End With
This message was edited by Autolycus on 2002-03-04 15:47


Or perhaps you need this :-

Dim rng As Range, col As Integer, cell As Range
Set rng = [A1:A4] 'revise as required
col = [B1:B30].Column 'revise as required
With rng
repeat:
.Formula = "=RANDBETWEEN(1,30)"
.Value = .Value
For Each cell In rng
With cell
If Application.WorksheetFunction.CountIf(rng, .Value) > 1 Then GoTo repeat
.Value = Cells(.Value, col)
End With
Next
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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