Random Numbers from list
 

HI,

I'm looking for a way to acomplish the following.


Let say I have 22 numbers in row 1 then I need a macro that will spit
out 10 random numbers out of those 22 numbers 100 times with no repeats.



I'm not good with loops so I dont even konw where to start.

any help is greatly appreciated.



Thanks,

Random Numbers from list
 

Here's a start. This takes numbers from range A1:A22. It ensures tha
the same cell content is only used once. 2 cells the same allow tha
number to be used again. Numbers are kept in the order they arise.

There is no check for duplication of sets. Copy/Paste Transposing, the
sorting each set, and on the sum of each, set gives an indication tha
this is unlikely.

'=======================================
Sub RANDOM()
Dim NumberArray(22)
Dim CheckDupArray(22)
Dim TempArray(22) ' only 10 used
Dim HitCount As Integer
Dim R As Integer
Dim ToRow As Long
'------------------------------
Application.Calculation = xlCalculationManual
Randomize
ToRow = 4
'-------------------------
'- get number list A1:A22
For c = 1 To 22
NumberArray(c) = ActiveSheet.Cells(1, c).Value
Next
'-----------------------------
'- MAIN LOOP
'-----------------------------
For N = 1 To 100
'- initialise arrays
HitCount = 1
For c = 1 To 22
CheckDupArray(c) = 0
TempArray(c) = 0
Next
'- get numbers
While HitCount <= 10
R = Int(22 * Rnd) + 1
If CheckDupArray(R) = 0 Then
CheckDupArray(R) = R
TempArray(HitCount) = R
HitCount = HitCount + 1
End If
Wend
'- show numbers
For c = 1 To 10
ActiveSheet.Cells(ToRow, c).Value = _
NumberArray(TempArray(c))
Next
ToRow = ToRow + 1
Next
'--------------------------------
Application.Calculation = xlCalculationAutomatic
MsgBox ("Done")
End Sub
'============================================

--
Message posted from http://www.ExcelForum.com