If you want to generate a list of unique random numbers then you can use RANDBETWEEN functions & VBA RND. In this article, we will generate 3 unique numbers from the provided list.
You can find original question here
Question: I want a macro to generate list of 3 random numbers from a list of numbers saved in column A. There is a condition that VBA code needs to be taken care of. The condition is there are 3 numbers that exist in column B, hence macro needs to create a list of numbers that have not occurred once in column B. In this way, we can have a list of unique numbers in column B & C.
Following is the snapshot of example that we have:
To get the list of unique random numbers; we need to follow the below steps to launch VB editor
Sub RandomNumbers() Dim ws As Worksheet Dim ar As Variant Dim RandomNum As Long Dim i As Integer Dim myVal As Long Randomize Set ws = ThisWorkbook.Sheets("Numbers") With ws ar = .Range("A" & Rows.Count).End(xlUp).Row .Range("C1:C3").ClearContents For i = 1 To 3 Do RandomNum = Int((1 - ar + 1) * Rnd + ar) myVal = .Range("A" & RandomNum).Value Loop Until Range("B1:C24").Find(what:=myVal, lookat:=xlWhole) Is Nothing .Range("C" & i).Value = myVal Next i End With End Sub
In the above code, we will use RND & INT function along with For loop & Do loop to get the result.
Conclusion: We can generate list of distinct random numbers from a list in Microsoft excel with small tweaking in the above VBA code.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.