random number selection
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: random number selection

  1. #1
    Guest

    Default

     
    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.

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Guest

    Default

    It definitely helps! Thanks!

  4. #4
    Guest

    Default

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

    Thanks!

  5. #5
    Guest

    Default

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

  6. #6

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com