Error Checking in Excel
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

Some videos you may like

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
  •