Thanks:  0
Likes:  0

# Thread: Lottery number generation: create a single line function.

1. close, but not close enough

you could still get 3 sevens or even all 6 as "14"

gotta be 6 different, from the same population (or 5 as that was the poster's version of his lottery)

o/

2. ive had this for about 2 year nevr had that bad results.. im upset they failed so bad your end... ermm.. like i said this is tough... a true master needs to solve this one.

3. oh, absolutely Jack... it serves as a good method as if you do get a repetition, you just hit F9 to recalc.... new numbers in a second.

The probablitiy of it ever bringing back the number 14 six times would be 1 in (49^6)

or 13,841,287,201 to 1

(same as Celtic winning the treble next year)

which means if you recalced every second, it would take 438 years before you get all numbers the same (on average)

trouble is, it's theoretically possible, so needs adjusting in terms of a solution.

Gawd, I forgot it's work tommorrow !

4. Well, since you bring Celtic into it. They've won the Treble 3 times in 114 years. So I guess that means they've got a 1 in 38 chance of winning it in any given year. However, since the League is a 2 horse race, as are the cups really, in any given year that basically gives them a 1 in 8 chance of winning the Treble next year. So not quite the 13,000,000 odd to 1. (which is about England's chance of winning the World Cup this year, without home soil advantage and dodgy linesman decisions)

To actually add something relevant to this thread. Why would you want to do this in 1 line of VBA? It would probably only be about 5 lines of VBA (possibly including the declarations). Less lines doesn't necessarily mean faster code.

5. All that and no guarantee of unique results? Excellent problem, but a bit of a letdown at the end. I was expecting someone to eat some tripe and eels.

Here is some VBA code that will generalize this problem. Originally posted by Tom Ogilvy. I have added some dim statements to declare all the variables used.

The comments in the sub show the originator of the algorithm that Tom adapted.

---begin VBA---
Sub GenNUniqueRandom()
'
' Algorithm from:
' The Art of Computer Programming: _
' SemiNumerical Algorithms Vol 2, 2nd Ed.
' Donald Knuth
' p. 139 [Algorithm P]
'
'
Dim list() As Long
Dim list1() As Long
Dim t As Long, i As Long, j As Long, k As Long
Dim lngTemp As Long
Dim num As Long
'Number of Unique Random Numbers you need
num = 40
'From a list of 1 to t numbers
t = 200
ReDim list(1 To t)
For i = 1 To t
list(i) = i
Next
j = t
Randomize
For i = 1 To t
k = Rnd() * j + 1
lngTemp = list(j)
list(j) = list(k)
list(k) = lngTemp
j = j - 1
Next
ReDim list1(1 To num)
For k = 1 To num
list1(k) = list(k)
Next
Range(Cells(1, 1), Cells(num, 1)).Value = _
Application.Transpose(list1)
End Sub
---end VBA---

Not a one line answer, but lightning fast.

This can be made into a UDF rather than a sub procedure.

Bye,
Jay

6. No way this can be done it one line, if so I'll eat some jellied eels as well

Code:
```Function RandLotto(Bottom As Integer, Top As Integer, Amount As Integer)
Dim iNum As String
Dim strNum As String
Dim i As Integer

Application.Volatile
iNum = Int((Top - Bottom + 1) * Rnd + Bottom)

For i = 1 To Amount
strNum = Trim(strNum & " " & iNum)
Do Until InStr(1, strNum, iNum) = 0
iNum = Int((Top - Bottom + 1) * Rnd + Bottom)
Loop
Next i

RandLotto= strNum

End Function```

_________________
Kind Regards
Dave Hawley
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-04-01 22:27 ]

7. EU and US,

Can I assign a random number to a string within a cell? For ex: =\$FirstPick(Randbetween(1,49)) without using VBA? This way numbers are stored in the buffer memory. Then I am able to retrieve the strings and compare two numbers. Or another way is to paste-value of the randbetween() to the surrounding cells and compare those cells. Sounds easy but it depends on Excel. I hope that makes sense.

T. Le

8. Hi Dave

remember we chewed this ober some 18 months ago.. MANY ways to do this... we all have our ways in VBA find i felt was best...

in function... ermmm... I still say YES it can be done, ive kinda solved and many others kinda have but in VBA its 100% thats what we need.

Still say can be done.. just bleive in Exel... how?????

any success?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•