Excel VBA Dominoes team selection randomly

[Rmejia]

I'm new on this and need help with a macro I need to create. The goal is to randomly select partners for dominoes matches from a list of names.

Details:
I will have a list of players in column "B", every day could be a different amount of players and different names. In column "A" those players will have a unique number. The macro should combine those players in pairs and those partners will go against another pair both pairs randomly selected. I will specify on a cell how many times each player will play on that day. The constraints are that:

1. No two players should play together on a team for the second time until all combinations have been used, and so on.
   
2. No two same teams should play against each other for the second time until all combinations have been used.
   

What I need back from the macro is in …

• Column D: Team 1, (Player 1 number).
• Column E: Team 1, (Player 2 number).
• Column F: Team 2, (Player 1 number).
• Column G: Team 2, (Player 2 number).

I only need the number since my plan is to get the name from the master list with a vlookup. Each row will be a different match. If for example, I have 8 players and each will play 5 times, at the end, I should have 10 rows with the matches:

8 players x 5 games per player =40/4 players each match =10
Please let me know if you have any questions, Thanks.

This is the code i have so far:

Sub SelectRandomPlayers()

Dim players()AsString, selectedPlayers(3)AsStringDim playerRange As RangeDim i AsLong, c AsLong, writeRow AsLong, ub AsLong, selectedIndex AsLong, j AsLong, ub2 AsLong

writeRow =2

Set playerRange = Range(Range("A2"), Range("A2").End(xlDown))
c = playerRange.count
ub = c -1ReDim players(ub)

For i =0To ub
players(i)= playerRange.Cells(i +1).Value2
Next i

DoFor i =0To3
ub2 = UBound(players)
selectedIndex = WorksheetFunction.RandBetween(0, ub2)
selectedPlayers(i)= players(selectedIndex)

For j = selectedIndex To ub2If j < ub2 Then players(j)= players(j +1)Next j

If ub2 >0ThenReDimPreserve players(ub2 -1)Next i

Range("D"& writeRow).Value2 = selectedPlayers(0)
Range("E"& writeRow).Value2 = selectedPlayers(1)
Range("F"& writeRow).Value2 = selectedPlayers(2)
Range("G"& writeRow).Value2 = selectedPlayers(3)
writeRow = writeRow +1LoopWhile UBound(players)>0
EndSub

I still can't figure how to change it so I can have more than one match per player and comply with the constraints.

 

[NARAYANK991]

Hi ,

Since code has to finally operate on data , even if code can be written without access to the data , at least for testing purposes , who ever writes the code will need data ; if you do not upload this data , then the data will have to be generated at our end.

You can save us this additional step by uploading your workbook with the data in it.

Narayan

 

[Marc L]

Hi Narayank991 !

Yes ! When I see this kind of thread,
specially without "Hello" neither "Please, could you …"
and with that kind of bold text,
I do not ever stop and move to next one …

Good week-end to all !
​

 

[NARAYANK991]

Hi ,

Since this question has been cross-posted in other forums such as MrExcel and StackOverflow , and since some answers / suggestions have been provided there , it is recommended that anyone wanting to help may go through the following :

http://stackoverflow.com/questions/43904604/excel-vba-dominoes-team-selection-randomly

https://www.mrexcel.com/forum/excel-questions/1004849-dominoes-team-selection-randomly.html

Thanks to p45cal for the information.

Narayan

 

[Rmejia]

Hi Narayank991 !

Yes ! When I see this kind of thread,
specially without "Hello" neither "Please, could you …"
and with that kind of bold text,
I do not ever stop and move to next one …

Good week-end to all !
​

You are 100% correct, I'm sorry for my rudeness, I will keep it in mind for next time. Like I said I'm new on this I thought that you go straight to business on forums.

 

[Rmejia]

Hi ,

Since code has to finally operate on data , even if code can be written without access to the data , at least for testing purposes , who ever writes the code will need data ; if you do not upload this data , then the data will have to be generated at our end.

You can save us this additional step by uploading your workbook with the data in it.

Narayan

Thanks, here is the workbook requested.

 

[NARAYANK991]

Hi ,

I am sorry , but I am not able to see how the code can implement the constraint that no player can play more than a pre-defined number of games.

Narayan

 

[Derek McGill]

Hi ,

I am sorry , but I am not able to see how the code can implement the constraint that no player can play more than a pre-defined number of games.

Narayan

As a chess player and club organiser for over 30 years this post has also got me stumped.