Unique random number generator VBA code issue

Question

I am trying to make a lottery generator of 5 box, idea is each box will have unique number every time I randomize it,

My code

If TextBox1.Value = TextBox2.Value Or TextBox1.Value = TextBox3.Value Or TextBox1.Value = TextBox4.Value Or TextBox1.Value = TextBox5.Value Then
    TextBox1.Value = TextBox1.Value + 1
ElseIf TextBox2.Value = TextBox1.Value Or TextBox2.Value = TextBox3.Value Or TextBox2.Value = TextBox4.Value Or TextBox2.Value = TextBox5.Value Then
    TextBox2.Value = TextBox2.Value + 1
ElseIf TextBox3.Value = TextBox1.Value Or TextBox3.Value = TextBox2.Value Or TextBox3.Value = TextBox4.Value Or TextBox3.Value = TextBox5.Value Then
    TextBox3.Value = TextBox3.Value + 1
ElseIf TextBox4.Value = TextBox1.Value Or TextBox4.Value = TextBox2.Value Or TextBox4.Value = TextBox3.Value Or TextBox4.Value = TextBox5.Value Then
    TextBox4.Value = TextBox4.Value + 1
ElseIf TextBox5.Value = TextBox1.Value Or TextBox5.Value = TextBox2.Value Or TextBox5.Value = TextBox3.Value Or TextBox5.Value = TextBox4.Value Then
    TextBox5.Value = TextBox5.Value + 1
End If

Kindly focus on the highlighted code, this is my code to make unique random number in each 5 boxes, but, sometimes 1 in 100 it gives me the same number for any two box

Use a [shuffle](https://stackoverflow.com/q/18543169/445425) (get a list of all possible values, shuffle it, then take 5 off the top) — chris neilsen, Aug 27 '21 at 12:31

score 1 · Answer 1 · edited Apr 20 '23 at 21:44

1

If you have Office 365 and you want each textbox to have a random value from 1 to 100 with no repeats, then generate an array with:

With Application.WorksheetFunction
        arr = .SortBy(.Sequence(100), .RandArray(100), 1)
End With

And then:

TextBox1.Value = arr(1,1)
TextBox2.Value = arr(2,1)
TextBox3.Value = arr(3,1)
TextBox4.Value = arr(4,1)
TextBox5.Value = arr(5,1)

edited Apr 20 '23 at 21:44

Peter Mortensen

30,738
21
105
131

answered Aug 27 '21 at 12:55

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · answered Aug 27 '21 at 12:58

Another possibility is to use a formula on the sheet, itself...

A1..An =RAND(), B1..Bn =1..n

Then sort on the A column - top five numbers will float to the top.

The beauty of this method is that every time you sort, the numbers are changed (pseudo) randomly again. (And no VBA necessary!)

Unique random number generator VBA code issue

2 Answers2