random numbers

  • Can someone please tell me how to generate randon numbers from data on sheet 2 which do not repeat themselves. eg sheet 2 data numbers 1 to 20
    random numbers should be 3,15,7,9,1,,,,
    not 3,15,7,7,9,3,,,,,

  • This generates a list of numbers from 1 to 20 in random order on sheet 2. Not sure if it is what you want, but it might help

    1. Public Sub main()
    2. Dim iRandArray() As Integer
    3. Dim i As Integer
    4. RandomOrder 20, iRandArray()
    5. For i = 1 To UBound(iRandArray)
    6. Sheets("Sheet2").Range("A1").Offset(i, 0).Value = iRandArray(i)
    7. Next
    8. End Sub

  • Here's a formula you can use. In this example, enter the numbers 1 thru 20 in cells A1:A20 on Sheet2 and in cells B1:B20 enter the formula =Rand()

    Then use this formula:

  • Thanks for the help. I used btadams version and it works a treat. Its actually just for my footy tipping.


  • Re: random numbers

    This is *possibly* a simpler way...

    In cells A1:A20 on Sheet2 put the formula;


    In cell A1 on Sheet1 put this formula;


    Then copy cell A1 on Sheet1 thru to cell A20 on Sheet1.

    Good Luck!