Adding a Unique Random Number within a Range
With the assistance of Excel VBA you can add a unique random number between two data points. For example, you could generate random numbers between 1 and 10 and list the sequence in an Excel range. So every number only appeared once and will not repeat itself. This is very helpful when attempting to generate unique list to test conditions in Excel. The following is the procedure which needs to be placed in a regular module in VBA. The Excel file below is also a good starting point.
The following video takes you through how the number generator works.
The following is an example of the attached file. You place a low number and a high number in the list. Excel generates the list in quick time. In the below example I put 1 as the low number and 10 as the high number. It produces a unique list of numbers seamlessly.
The following is the code which
The following is a detailed description of the coding using the scripting dictionary. Hope this helps.
The following is the Excel VBA code used to generated a unique list of non repeating numbers.
Sub ProduceUniqRandom() 'Excel VBA to generate random number
Dim myEnd As Long
Dim i As Long
Dim a() 'Excel Dynamic Variable
Dim sh As Worksheet
Set sh=Sheet1
myStart=sh.[A10]
myEnd=sh.[B10]
ReDim a(0 To myEnd - myStart)
With Createobject("System.Collections.SortedList")
For i=myStart To myEnd
For i=0 To .Count - 1
sh.Range("A5").Resize(UBound(a) + 1).Value=Application.Transpose(a)
The following file outlines the procedure. It works remarkably fast to generate non repeating numbers. I hope the file is everything your were looking for.
