random number selection

[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.

 

[Klefty]

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.

 

[Guest]

It definitely helps! Thanks!

 

[Guest]

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

Thanks!

 

[Guest]

any ideas to the previous posting? I'm struggling over here....

 

[Legacy 344]

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

 

[Klefty]

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

 

[Legacy 344]

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