Lottery Pairs

[ststern45]

Hi everyone,

Need help with creating VBA for creating lottery pairs

Sheet1
Cell A1 = 1
Cell B1 = 2
Cell C1 = 3
Cell D1 = 4
Cell E1 = 5

I would like to know how to generate pairs other than using formulas for placing the pairs for the above set starting in cell G1
Cell G1 = 1,2
Cell H1 = 1,3
Cell I1 = 1,4
Cell J1 = 1,5
Cell K1 = 2,3
Cell L1 = 2,4
Cell M1 = 2,5
Cell N1 = 3,4
Cell O1 = 3,5
Cell P1 = 4,5

My random list of sets would would be around 100 in cell range A1 through E1 down to A100:E100

Thank you in advance!!

 

[ststern45]

The code below would take the numbers 1 through 49 in cell range A1 through A49 and generate the pairs in cells D1:E1 going downward. How can I change the code to generate the pairs across adding a coma between the 2 number (1,2) in 1 cell and loop through 100 random sets from A1:E1 through A100:E100?

Thank you


Option Explicit
Option Base 1
Sub Combin_2N()
Dim D As Integer, E As Integer
Dim I As Long, J As Integer, vN(49) As Integer
Application.ScreenUpdating = False
Range("A1").Select
J = 1
Do While ActiveCell > 0
vN(J) = ActiveCell.Value
J = J + 1
ActiveCell.Offset(1, 0).Select
Loop
J = J - 1
Range("C1").Select
I = 1
For D = 1 To J - 1
For E = D + 1 To J

ActiveCell.Offset(0, 0).Value = I
ActiveCell.Offset(0, 1).Value = vN(D)
ActiveCell.Offset(0, 2).Value = vN(E)

I = I + 1
ActiveCell.Offset(1, 0).Select

Next E
Next D
Range("A1").Select
End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG29Dec22
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = 7
    [COLOR="Navy"]For[/COLOR] n = 1 To 5
        [COLOR="Navy"]For[/COLOR] nn = n + 1 To 5
            c = c + 1
            Cells(Dn.Row, c) = Dn(, n) & "," & Dn(, nn)
        [COLOR="Navy"]Next[/COLOR] nn
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ststern45]

Thanks Mick,

Works great.

Happy New Year

 

[MickG]

You're welcome