How to Generate 5/59 lotto numbers in 1 cell

[testing lotto]

I am looking for a way to generate 5 random lottery numbers sorted in numerical order in one cell, not 5 cells. for example, 03-13-22-39-51 contained in cell A1. Not 03 in cell A1 and 13 in B1, 22 in C1, 39 in D1, 51 in E1. I think it should be possible to do this ?

 

[shg]

Why?

It seems likely that the next question is how to operate on five data elements in the same cell.

 

[testing lotto]

Wow you read my mind, you are good... thanks man

 

[testing lotto]

This works but crashes randomly ? (go figure) I think my sort routine is at least part of the problem.

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, T As Long
Sub List_5x59()
' Macro to Count the number of tries it takes to match lotto #s
T = 0
Sheets("Sheet1").Select
Selection.ColumnWidth = 14
Application.ScreenUpdating = True
Randomize
5
Range("B1").Select
' Start of loops for 5 variables (A-E)
10
A = Int(55 * Rnd + 1)
B = Int(56 * Rnd + 1)
C = Int(57 * Rnd + 1)
D = Int(58 * Rnd + 1)
E = Int(59 * Rnd + 1)
If A >= B Then GoTo 10
If B >= C Then GoTo 10
If C >= D Then GoTo 10
If D >= E Then GoTo 10
' Copying the combination in the form of N1-N2-N3-N4-N5 into cell "B1"
T = T + 1 'incriment # of Tries.
ActiveCell.Value = Application.WorksheetFunction.Text(A, "00") & "-" _
& Application.WorksheetFunction.Text(B, "00") & "-" _
& Application.WorksheetFunction.Text(C, "00") & "-" _
& Application.WorksheetFunction.Text(D, "00") & "-" _
& Application.WorksheetFunction.Text(E, "00") & ""
Range("C1").Select
ActiveCell.Value = T
If ("A1") = ("B1") Then GoTo 14
GoTo 5
'Move down to the next set of numbers.
14
T = 0
15
Range("B2").Select
' Start of loops for 5 variables (A-E)
20
Randomize
A = Int(59 * Rnd + 1)
B = Int(59 * Rnd + 1)
C = Int(59 * Rnd + 1)
D = Int(59 * Rnd + 1)
E = Int(59 * Rnd + 1)
If A >= B Then GoTo 20
If B >= C Then GoTo 20
If C >= D Then GoTo 20
If D >= E Then GoTo 20
' Copying the combination in the form of N1-N2-N3-N4-N5 into cell "B1"
T = T + 1 'incriment # of Tries.
ActiveCell.Value = Application.WorksheetFunction.Text(A, "00") & "-" _
& Application.WorksheetFunction.Text(B, "00") & "-" _
& Application.WorksheetFunction.Text(C, "00") & "-" _
& Application.WorksheetFunction.Text(D, "00") & "-" _
& Application.WorksheetFunction.Text(E, "00") & ""
Range("C2").Select
ActiveCell.Value = T
If ("A2") = ("B2") Then End
GoTo 15
End Sub

 

[shg]

Function TL(n As Long, m As Long, Optional bvolatile As Boolean = False) As String
    Dim ai() As Long
    Dim i As Long
    
    If bvolatile Then Application.Volatile
    
    ai = aiDecToCombo(n, m, Int(Rnd * nComb(n, m)))
    For i = 1 To m
        TL = Format(ai(i) + 1, "00") & "," & TL
    Next i
        
    TL = Left(TL, Len(TL) - 1)
End Function
 
Function aiDecToCombo(ByVal n As Long, ByVal m As Long, ByVal cNum As Long) As Long()
    ' shg 2009-12
    ' VBA or UDF
 
    ' Does the opposite of iComboToDec; i.e., given a combination number
    ' [0, nComb(n,m)-1], returns the combination. n is not strictly necessary
    ' to the computation, but makes it faster.
 
    ' E.g., for 5 choose 3, the first (0th) combination is {2,1,0}, and
    '                       the last  (9th) combination is {4,3,2}
 
    Dim ai()        As Long
    Dim i           As Long
    Dim j           As Long
 
    cNum = cNum Mod nComb(n, m)
    ReDim ai(1 To m)
 
    For i = m To 1 Step -1
        Do
            n = n - 1
            j = nComb(n, i)
        Loop While j > cNum
        ai(m - i + 1) = n
        cNum = cNum - j
    Next i
 
    aiDecToCombo = ai
End Function
 
Function nComb(n As Long, m As Long) As Long
    ' UDF or VBA
    ' Returns the number of combinations of n choose m
    ' Just like Combin(n, m), except returns
    ' 0 instead of #NUM! for invalid inputs
 
    If m >= 0 And n >= m Then nComb = WorksheetFunction.Combin(n, m)
End Function

E.g., =TL(59, 5)

 

[testing lotto]

I don't understand any of your code ?

 

[testing lotto]

I get a compile error.

expected end sub.

 

[Armando Montes]

Try this:

Function RandLotto(Bottom As Integer, Top As Integer, _
                    Amount As Integer) As String
    Dim iArr As Variant
    Dim i As Integer
    Dim r As Integer
    Dim temp As Integer
    
    Application.Volatile
    
    ReDim iArr(Bottom To Top)
    For i = Bottom To Top
        iArr(i) = i
    Next i
    
    For i = Top To Bottom + 1 Step -1
        r = Int(Rnd() * (i - Bottom + 1)) + Bottom
        temp = iArr(r)
        iArr(r) = iArr(i)
        iArr(i) = temp
    Next i
    
    For i = Bottom To Bottom + Amount - 1
        RandLotto = RandLotto & " " & iArr(i)
    Next i
    
    RandLotto = Trim(RandLotto)
    
End Function

 

[testing lotto]

Same Compile Error

Expected End Sub ?

 

[testing lotto]

My only programming experience is with qbasic or gwbasic about 20 years ago, I am hacking my way through this vba stuff. I must be missing or skipping something?

Thanks for the help !