Non Repeating Random Number Generator

**Bob1980** · 11-27-2012, 07:01 PM

Hello Everyone,

First off, thanks for looking at this post. I'm relatively new to coding in VBA and am starting to get a little bit of it. But, I have this random number generator (I didn't code it) that I've been using. Unfortunately, it's been crashing when I've been trying to modify it. I think the chance of it crashing increases as the range of random numbers decrease in proportion to the amount of numbers you want to generate increases.

In any event, here's what I would like the code to do:
1. List numbers 1-50
2. List all 50 numbers random
3. No repeats.

Below is the code:

Option Explicit

Sub Random()
    Dim x As Long, y As Long, z As Long, tempnum As Long
    Dim flag As Boolean
    Dim i As Integer
    Dim foundCell As Range
    Application.ScreenUpdating = False
    x = Application.InputBox("Enter starting Random Number", "Random Number Generation", 1, , , , , 1)
    y = Application.InputBox("Enter ending Random Number", "Random Number Generation", 1000, , , , , 1)
    z = Application.InputBox("How many random numbers would" & "you like to generate (<15000)?", "Random Number Generation", 100, , , , , 1)
    If z = 0 Then Exit Sub
    If z > 15000 Then z = 15000
    If z > y - x + 1 Then
        MsgBox "You specified more numbers to return than " & "are possible in the range!"
        Exit Sub
    End If
    Randomize
    Cells(1, 1) = Int((y - x + 1) * Rnd + x)
    For i = 2 To z
        Do
            flag = False
            Randomize
            tempnum = Int((y - x + 1) * Rnd + x)
            Set foundCell = Range("a1", Range("a1").End(xlDown).Address).Find(tempnum)
            If Not (foundCell Is Nothing) Then
                flag = True
            End If
        Loop Until Not flag
        Cells(i, 1) = tempnum
    Next
End Sub

**Bob1980** · 11-27-2012, 07:13 PM

The attachment contains the file with the macro.

**Richard Buttrey** · 11-27-2012, 07:35 PM

Hi,

Are you just wanting to list the numbers 1 to 50 in a random order or generate 50 non repeating random numbers?

**jindon** · 11-27-2012, 08:31 PM

Try

Option Explicit

Sub test()
    Dim x As Long, y As Long, z As Long
    Dim i As Long, a
    x = Application.InputBox("Enter starting Random Number", "Random Number Generation", 1, , , , , 1)
    y = Application.InputBox("Enter ending Random Number", "Random Number Generation", 1000, , , , , 1)
    z = Application.InputBox("How many random numbers" & vbLf & "would you like to generate (<15000)?", _
        "Random Number Generation", 100, , , , , 1)
    If z = 0 Then Exit Sub
    If z > 15000 Then z = 15000
    If z > y - x + 1 Then
        MsgBox "You specified more numbers to return than " & "are possible in the range!"
        Exit Sub
    End If
    ReDim a(1 To y - x + 1, 1 To 2)
    Randomize
    For i = x To y
        a(i - x + 1, 1) = i
        a(i - x + 1, 2) = Rnd
    Next
    VSortM a, 1, UBound(a, 1), 2
    With Cells(1).Resize(z)
        .CurrentRegion.ClearContents
        .Value = a
    End With
End Sub

Private Sub VSortM(ary, LB, UB, ref)
    Dim M As Variant, i As Long, ii As Long, iii As Long, temp
    i = UB: ii = LB
    M = ary(Int((LB + UB) / 2), ref)
    Do While ii <= i
        Do While ary(ii, ref) < M: ii = ii + 1: Loop
        Do While ary(i, ref) > M: i = i - 1: Loop
        If ii <= i Then
            For iii = LBound(ary, 2) To UBound(ary, 2)
                temp = ary(ii, iii): ary(ii, iii) = ary(i, iii)
                ary(i, iii) = temp
            Next
            ii = ii + 1: i = i - 1
         End If
    Loop
    If LB < i Then VSortM ary, LB, i, ref
    If ii < UB Then VSortM ary, ii, UB, ref
End Sub

**Trebor76** · 11-27-2012, 09:55 PM

I have a numbers game on my website that is probably what you're after.

**Bob1980** · 11-28-2012, 10:12 AM

Hi Guru,

Thanks a bunch. It worked perfectly!

Thanks,
Bob1980

Non Repeating Random Number Generator

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Non Repeating Random Number Generator

Re: Non Repeating Random Number Generator

Re: Non Repeating Random Number Generator

Re: Non Repeating Random Number Generator

Re: Non Repeating Random Number Generator

Re: Non Repeating Random Number Generator

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions