Lottery Pairs

ststern45

Well-known Member
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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ststern45

Well-known Member
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

MrExcel MVP
Try this:-
Code:
``````[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

Thanks Mick,

Works great.

Happy New Year

You're welcome

Replies
0
Views
103
Replies
12
Views
341
Replies
12
Views
610
Replies
12
Views
1K
Replies
0
Views
703

1,191,120
Messages
5,984,759
Members
439,909
Latest member
daigoku

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back