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,,,,
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 helpCodeCode
Private Sub RandomOrder(ByVal iNumRange As Integer, ByRef iRandArray() As Integer) Dim iNumArray() As Integer Dim iRandValue As Integer ReDim iNumArray(1 To iNumRange, 1 To 2) ReDim iRandArray(1 To iNumRange) For i = 1 To iNumRange iNumArray(i, 1) = i iNumArray(i, 2) = 1 Next For i = 1 To iNumRange Randomize iRandValue = Int((iNumRange * Rnd) + 1) If iNumArray(iRandValue, 2) = 0 Then i = i - 1 Else iRandArray(i) = iRandValue iNumArray(iRandValue, 2) = 0 End If Next 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.