Random Number Help

**rede96** · 10-13-2009, 11:18 AM

Hi,

How can I make sure that I can generate random numbers that don't repeat using the rnd function in VBA please?

Thanks

**sweep** · 10-13-2009, 12:00 PM

http://www.ozgrid.com/VBA/RandomNumbers.htm

**shg** · 10-13-2009, 12:30 PM

See VBE Help for Randomize.

**rede96** · 10-13-2009, 06:12 PM

Originally Posted by sweep

http://www.ozgrid.com/VBA/RandomNumbers.htm

Seen that thanks. I used some of the code.

Originally Posted by shg

See VBE Help for Randomize.

Thanks for the tip. I did already look this up but it doesn't solve my problem.

In short, I wrote a small bit of code that generates 6 lottery numbers between 1 and 49 and then it checks for a match against 6 lotto numbers I set manually. If no match then it generates another 6 numbers etc. etc. until a match is found.

I was using the code to do some statistical analysis.

The problem is I can demonstrate that the numbers that the vba code generate cannot be random or are random within a certain limits.

This is because I can have 6 lotto numbers where a match is found each time, within a million tries or so and 6 lotto numbers where no match is found, even after 100 million tries, using randomize.

Statistically, that shouldn't happen.

Anyway, I just wanted to know if I'd done the code wrong or something.

**shg** · 10-13-2009, 06:46 PM

Anyway, I just wanted to know if I'd done the code wrong or something.

Maybe (probably). Post your code.

Excel's random number generator 2003 and later is very good.

**rede96** · 10-14-2009, 02:52 PM

Originally Posted by shg

Maybe (probably). Post your code.

Excel's random number generator 2003 and later is very good.

Doh!

Sorry about that.

Code is below. I am using excel 2007

Sub LottoTest()
Randomize
Dim CountX As Long
Dim temp As Double
Dim k As Long
Dim j As Long
Dim i As Long
Dim l As Long
Dim sorted As Boolean
Dim x As Long
Dim MyTemp As String
Dim Arr(1 To 6) As Long
CountX = 1 ' this variable counts how many tries it takes.
Dim MyLotto As String
MyLotto = "1,16,27,29,35,38" 'Test lotto numbers to match
Dim MyView As String
'start generating lotto numbers until a match is found or stop after 10,000,000 tries
Do
    'generate 6 unique lotto numbers
    For i = 1 To 6
        Arr(i) = Int((49 - 1 + 1) * Rnd + 1)
            If i > 1 Then 'check for a duplicate number
CheckDuplicate:
                For l = 1 To i - 1
                    If Arr(i) = Arr(l) Then
                    Arr(i) = Int((49 - 1 + 1) * Rnd + 1) 'if duplicate exits, generate another number
                    GoTo CheckDuplicate 're-check for duplicate
                    End If
                Next l
        End If
    Next i
    'some code that sorts the array
    sorted = False
    Do While Not sorted
        sorted = True
            For x = 1 To UBound(Arr) - 1
                If Arr(x) > Arr(x + 1) Then
                    temp = Arr(x + 1)
                    Arr(x + 1) = Arr(x)
                    Arr(x) = temp
                    sorted = False
                End If
            Next x
    Loop
    'store the 6 numbers as a string to compare against test numbers
    MyTemp = Arr(1) & "," & Arr(2) & "," & Arr(3) & "," & _
    Arr(4) & "," & Arr(5) & "," & Arr(6)
        If MyTemp = MyLotto Then 'if a match then show numbers and how many tries
            MyView = MyLotto & " took " & CountX & " tries"
            MsgBox MyView
            Exit Sub
        End If
    CountX = CountX + 1 'counts the number of tries
Loop Until CountX = 10000000 'stops code after 10,000,000 failed attempts.
End Sub

**arlu1201** · 06-07-2013, 04:42 AM

Alex,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Also,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

**AlexFromFrance** · 06-07-2013, 09:17 AM

Dear Arlu,

Thank you for your information, I am a new comer...

I did'nt posted any question, it was a solution to this thread.
You have the code to test it and if some people have questions according to this code and what I tried to explain, they just ask me or send an email. I was just trying to help.

Best regards,
Alex