Generate list of numbers in random order using VBA

[bluefeather8989]

I need VBA to make a list of numbers in A:A from 101 to 132 in random order. each number to be used only once

 

[JGordon11]

Function RandArrayNoRepeats(startNo As Long, endNo As Long)
    Dim i As Long, tot As Long, b
    tot = endNo - startNo + 1
    ReDim a(1 To tot, 1 To 2)
    For i = 1 To tot
        a(i, 2) = Rnd()
        a(i, 1) = startNo + i - 1
    Next
    b = Application.Sort(a, 2)
    ReDim Preserve b(1 To tot, 1 To 1)
    RandArrayNoRepeats = b
End Function

Book2
	A	B	C	D
1	127		101
2	124		102
3	116		103
4	109		104
5	129		105
6	108		106
7	119		107
8	110		108
9	130		109
10	102		110
11	113		111
12	106		112
13	118		113
14	105		114
15	131		115
16	101		116
17	120		117
18	123		118
19	125		119
20	114		120
21	107		121
22	111		122
23	104		123
24	115		124
25	117		125
26	132		126
27	121		127
28	103		128
29	122		129
30	112		130
31	128		131
32	126		132
33
Sheet3

Cell Formulas
Range	Formula
A1:A32	A1	=RandArrayNoRepeats(101,132)
C1:C32	C1	=SORT(A1#)
Dynamic array formulas.

 

[Peter_SSs]

FWIW you could eliminate the b variable and the last part of the function could be achieved directly as follows

Function RandArrayNoRepeats(startNo As Long, endNo As Long)
    Dim i As Long, tot As Long ', b
    tot = endNo - startNo + 1
    ReDim a(1 To tot, 1 To 2)
    For i = 1 To tot
        a(i, 2) = Rnd()
        a(i, 1) = startNo + i - 1
    Next
'    b = Application.Sort(a, 2)
'    ReDim Preserve b(1 To tot, 1 To 1)
    RandArrayNoRepeats = Application.Index(Application.Sort(a, 2), 0, 1)
End Function

 

[Marc L]

The easy swap VBA demonstration :​

Sub Demo1()
    Dim V, L&, R&, S
        V = [ROW(101:132)]
    For L = UBound(V) To 2 Step -1
        R = Application.RandBetween(1, L)
        S = V(L, 1):  V(L, 1) = V(R, 1):  V(R, 1) = S
    Next
        [A1].Resize(UBound(V)).Value2 = V
End Sub

 

[BSALV]

The easy swap VBA demonstration

Personal opinion/feeling without proof, i think this method is less random than the others, but i don't know how to prove it.
Run that macro 1,000 or 1,000,000 times and check duplicates ???

Perhaps a question for the gurus here, with rnd(), it's better to use Randomize.
Is that also the case with RandBetween and for the new RandArray ?

 

[Marc L]

I have read a long time ago that the VBA Rnd function was the worse randomizer ever created​

but in many posts on many forums I often used it 'cause I was often under Excel 2003 version.​

In fact my demonstration is a swap shuffle method …​

​

Important to remind :​

Rnd must be initialized with the Randomize statement or each time the same 'random' sequence will be generated !​

No such issue using the native worksheet functions the reason why I use it or 'cause I was lazy ! (The lazy the smarter ? )​

 

[BSALV]

The lazy the smarter ?

great !
The new RandArray (2021 & 365, not in your 2010 !) is better random and is specially made for lazy people.
Ask for an array of 1,000 elements random integer between 101 and 132 (with repetition), a fraction of a second later, you got them.

And now, back to the topicstarter, we 're hijacking him.

 

[bluefeather8989]

The easy swap VBA demonstration :​

Sub Demo1()
    Dim V, L&, R&, S
        V = [ROW(101:132)]
    For L = UBound(V) To 2 Step -1
        R = Application.RandBetween(1, L)
        S = V(L, 1):  V(L, 1) = V(R, 1):  V(R, 1) = S
    Next
        [A1].Resize(UBound(V)).Value2 = V
End Sub

Thanks this is what I need.
How do I change the location. I know i said A;A but what if I need to change that to a specific range
?

 

[Marc L]

​

You can change the range destination within the codeline located just after the Next codeline …​

 

[mountainfish]

The easy swap VBA demonstration :​

Sub Demo1()
    Dim V, L&, R&, S
        V = [ROW(101:132)]
    For L = UBound(V) To 2 Step -1
        R = Application.RandBetween(1, L)
        S = V(L, 1):  V(L, 1) = V(R, 1):  V(R, 1) = S
    Next
        [A1].Resize(UBound(V)).Value2 = V
End Sub

So, i spent a good little bit of time picking this apart to try to understand what was going on! When I had that "aha!" moment it made my day (I must be a nerd). Anyhow, thank you for sharing. It works just like I need it to.