Alright, I know I am opening myself to abuse here because this method is probably not the easiest or the best. Also, it is very lightly tested, but it's a rough draft.
These two functions together should create a random array. It can be a unique random array, or a duplicated random array. Here are the two functions: <span style="color: darkblue; font-family: courier">GENERATERANDOMARRAY</span> and <span style="color: darkblue; font-family: courier">GENERATERANDOM</span>
<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> GenerateRandomArray(lowvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, highvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, quantity <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> unique <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> currentCount
<SPAN style="color:#00007F">Dim</SPAN> tmpArry() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> tmpColl <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> Collection
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">If</SPAN> quantity <= 0 <SPAN style="color:#00007F">Then</SPAN> GenerateRandomArray = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrRef): <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">ReDim</SPAN> tmpArry(quantity - 1) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> currentCount < quantity
<SPAN style="color:#00007F">Dim</SPAN> tmpRan
<SPAN style="color:#00007F">If</SPAN> unique <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">Do</SPAN>
tmpRan = GenerateRandom(lowvalue, highvalue)
Err.Clear
tmpColl.Add Item:=tmpRan, key:=CStr(tmpRan)
<SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Err.Number = 0
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
<SPAN style="color:#00007F">Else</SPAN>
tmpRan = GenerateRandom(lowvalue, highvalue)
tmpArry(currentCount) = tmpRan
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
currentCount = currentCount + 1
<SPAN style="color:#00007F">Loop</SPAN>
<SPAN style="color:#00007F">If</SPAN> unique <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> quantity
tmpArry(i - 1) = tmpColl(i)
<SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
GenerateRandomArray = tmpArry
<SPAN style="color:#00007F">Set</SPAN> tmpColl = <SPAN style="color:#00007F">Nothing</SPAN>
Erase tmpArry
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">Function</SPAN> GenerateRandom(lowvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, highvalue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">If</SPAN> highvalue <= lowvalue <SPAN style="color:#00007F">Then</SPAN> GenerateRandom = <SPAN style="color:#00007F">CVErr</SPAN>(xlErrNA): <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
Randomize
GenerateRandom = Int((highvalue - lowvalue + 1) * Rnd + lowvalue)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
Also, here is a short example of how to use these in a procedure.
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ListRandomArray()
<SPAN style="color:#00007F">Dim</SPAN> RandomArry
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
RandomArry = GenerateRandomArray(1, 2000, 900, <SPAN style="color:#00007F">False</SPAN>)
<SPAN style="color:#00007F">For</SPAN> i = <SPAN style="color:#00007F">LBound</SPAN>(RandomArry) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(RandomArry)
ActiveCell = RandomArry(i)
ActiveCell.Offset(1, 0).Activate
<SPAN style="color:#00007F">Next</SPAN>
Erase RandomArry
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>