[COLOR="Navy"]Sub[/COLOR] MG18May11
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Randomize
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]Do[/COLOR] Until .Count = 5
num = Range("A" & Int(Rnd * 50) + 1).value
[COLOR="Navy"]If[/COLOR] Not .Exists(num) [COLOR="Navy"]Then[/COLOR]
.Add num, ""
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Loop[/COLOR]
Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Try:-
Regards MickCode:[COLOR="Navy"]Sub[/COLOR] MG18May11 [COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR] Randomize [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary") .CompareMode = vbTextCompare [COLOR="Navy"]Do[/COLOR] Until .Count = 5 num = Range("A" & Int(Rnd * 50) + 1).value [COLOR="Navy"]If[/COLOR] Not .Exists(num) [COLOR="Navy"]Then[/COLOR] .Add num, "" [COLOR="Navy"]End[/COLOR] If [COLOR="Navy"]Loop[/COLOR] Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys) [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR] [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
in column b next to your numbers use the =rand() functions and drag it down. then use this formula in column c or in your second sheet referencing the correct columns in sheet 1.
=INDEX(A1:A50, MATCH(SMALL(B1:B50, ROW()), B1:B50, 0))
... where A contains your values of interest, and B contains random numbers.
Try:-
Regards MickCode:[COLOR="Navy"]Sub[/COLOR] MG18May11 [COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR] Randomize [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary") .CompareMode = vbTextCompare [COLOR="Navy"]Do[/COLOR] Until .Count = 5 num = Range("A" & Int(Rnd * 50) + 1).value [COLOR="Navy"]If[/COLOR] Not .Exists(num) [COLOR="Navy"]Then[/COLOR] .Add num, "" [COLOR="Navy"]End[/COLOR] If [COLOR="Navy"]Loop[/COLOR] Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.keys) [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR] [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Hi Mike, running this macro gives me Run-Time error '6' Overflow and highlights "num = Range("A" & Int(Rnd * 50) + 1).value" in yellow
Change the variable type as below:-
MickCode:Dim num As Long