The usual request for Random numbers is how to generate random numbers without duplicates. While these may no longer be random numbers it is a frequent request. The following will work for small sets. It might be better to do something else for larger sets because checking every previous numbers will take twice as long for each additional number.
Volatile functions such as CELL() OFFSET() TODAY() INDIRECT() NOW() INFO() RAND() may impact your system, see slowresp.htm#volatile.
The routine below uses the worksheet function RANDBETWEEN to build an
array. (You will need a reference to Excel's Data Analysis Toolpak:
tools, addins, check Analysis Toolpak). Each
random number generated is compared to existing values in the array to
Dim MyRand(12), Invalid(12), iTest As Long Dim GoodNum As Boolean For x = 1 To 12 GoodNum = False Do While GoodNum = False iTest = RANDBETWEEN(1, 12) '-- not avaiable in VBA For y = 1 To 12 If iTest = MyRand(y) Then GoodNum = False Exit For Else GoodNum = True End If Next y Loop MyRand(x) = iTest Next x
For i = 1 to 12 activecell.offset(i-1,0) = MyRand(i) next i
Generate 1 to 180 down a column, and place =RAND() in the next empty column. Convert the random numbers to constants: copy (ctrl+c), edit, paste special, values and then sort on the random numbers column, finally eliminate the random numbers column.
Record a macro doing the above if you want to do this often.
I expect this would be more efficient than generating a random number between a range and then checking it to make sure that it doesn't duplicate a previous number.
Option Explicit Sub Macro11() 'create numbers 1 to 180 in column a, randomize the order Range("a1").Formula = "1" Range("a2").Formula = "2" Range("A1:A2").AutoFill Range("A1:A180") Range("b1").Formula = "=RAND()" Range("b1").AutoFill Range("B1:B180") Range("B1:B180").Select Columns("B:B").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Rows("1:180").Select Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("b:b").Delete Range("a1").Select End SubFWIW, Under 1 second on my machine, Upping from 180 to 64000 runs about 10 seconds.
Update: arrays are faster see replies by J.E.McGimpsey and David Braden in this thread The array version by J.E.McGimpsey brute force rearranging ran in under 1 second for 64,000 rows. probably runs faster by sorting arrays. Update: in the same thread Tushar Mehtar has variations and comments on timing, theoretical and resulting see his comments.
Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved