I have a list of 147 names in a column and when I run the following VBA code I get another list of 80 random names which is what I want, but I would like the names to change every time I run the macro. At the moment the random names list stays the same. Could anyone please help me. Sub Random_Name()
Dim Rng As Range
Dim Cell As Range
Dim rNum, gName, DupYN
Set Rng = Range("C2:C81")
Rng.ClearContents
For Each Cell In Rng
here:
rNum = Int(146 * Rnd + 1)
gName = Application.VLookup(rNum, Range("A2:B146"), 2, False)
DupYN = Application.WorksheetFunction. _
CountIf(Rng, gName)
If DupYN = 1 Or Cell.Value = gName Then
GoTo here
Else
Cell.Value = gName
End If
Next Cell
End Sub
Dim Rng As Range
Dim Cell As Range
Dim rNum, gName, DupYN
Set Rng = Range("C2:C81")
Rng.ClearContents
For Each Cell In Rng
here:
rNum = Int(146 * Rnd + 1)
gName = Application.VLookup(rNum, Range("A2:B146"), 2, False)
DupYN = Application.WorksheetFunction. _
CountIf(Rng, gName)
If DupYN = 1 Or Cell.Value = gName Then
GoTo here
Else
Cell.Value = gName
End If
Next Cell
End Sub