This Excel VBA tutorial explains how to generate non-duplicated random number using Excel VBA.
You may also want to read:
Generate random HKID Hong Kong ID
Excel VBA generate non-duplicated random number
In my previous post, I demonstrated how to assign random number to a value using Rank Function and Rand Function. In this post I will introduce a more direct approach to assign a random number with the help of VBA.
In this approach, each time after a random number is generated, VBA checks whether specific range already has the number. If it does, then regenerate another random number until the number is not repeated. The probability of generating some random numbers are dependent of previous numbers, this has statistics implication if you use it for simple random sampling.
VBA Code to generate non-duplicated random number
Click here to learn how to use Rnd Function to generate random number with defined lowerbound and upperbound.
Public Sub generateRandNum()
'Define your variabiles
lowerbound = 1
upperbound = 20000
Set randomrange = Range("A1:C5000")
randomrange.Clear
For Each rng1 In randomrange
counter = counter + 1
Next
If counter > upperbound - lowerbound + 1 Then
MsgBox ("Number of cells > number of unique random numbers")
Exit Sub
End If
For Each Rng In randomrange
randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Do While Application.WorksheetFunction.CountIf(randomrange, randnum) >= 1
randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
Loop
Rng.Value = randnum
Next
End Sub
Example – generate non-duplicated random number
Before running the above VBA, the first thing is to define the Excel Range in which you want to generate random number. You can generate multiple rows and columns. Second, define the upper range of lower range of the random number.
For example, I want to generate 10 random numbers in Range A1:A10, then you should define your variables as
lowerbound = 1 upperbound = 10 randomrange = "A1:A10"
Run the Macro and you get the below result
If you want to generate 10 random numbers but the range is defined as A1:A9, an error message will pop up because it doesn’t make sense to generate 9 unique numbers in 10 Cells.
If you don’t need the numbers to be unique, then you should simply use RandBetween Function for each Cell in A1:A10 but you will get duplicates.
It is also possible to use non-VBA solutions to generate non-duplicated random number, click here to learn more.
