Greetings,
In Excel 2010 I have 5 columns and 40 rows for 200 cells. I need to generate random numbers of 1 - 200 in the cells with no repeat of the numbers. Is this possible?
(Tipping Hat) T.I.A.
This forum has been moved to TeachExcel.com
Ask all future questions in the New Excel Forum.
ExcelKey
=INT(RAND()*($B$2-$A$2)+$A$2)
Sub RandNoRepeat()
Dim NumArray(1 To 200)
Dim RandGroup As Collection
Dim RandNum As Long
Dim UsedNum As Long
Dim Element As Variant
Dim AddWorkSheet As Worksheet
Dim i As Long, j As Long, k As Long
Application.ScreenUpdating = False
'Comment out the next line if you don't want to add
' a new worksheet each time you run the code.
Set AddWorkSheet = ThisWorkbook.Worksheets.Add
Set RandGroup = New Collection
For k = 1 To UBound(NumArray)
NumArray(k) = k
Next k
i = 1
For Each Element In NumArray
RandGroup.Add Element
i = i + 1
Next Element
With ActiveSheet
For i = 1 To 40
For j = 1 To 5
Randomize
UsedNum = Int(Rnd() * RandGroup.Count + 1)
RandNum = RandGroup(UsedNum)
.Cells(i, j) = RandNum
RandGroup.Remove (UsedNum)
Next j
Next i
End With
Application.ScreenUpdating = True
End Sub
Return to Macros and VBA Questions
Users browsing this forum: No registered users and 228 guests