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

    Public Sub main()
      Dim iRandArray() As Integer
      Dim i As Integer
      RandomOrder 20, iRandArray()
      For i = 1 To UBound(iRandArray)
        Sheets("Sheet2").Range("A1").Offset(i, 0).Value = iRandArray(i)
    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:

  • You'll want to set the range references to absolute when you fill the formula down:


  • 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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!