Creating a fixture list

G

Gilbo

I have used a macro to gererate fixtures for a league but it doesn't
calculate properly. All teams should play each other once, (ie 10 teams - 90
matches). I have used the following macro. Thanks for your help in advance.

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow - 1
For j = i + 1 To lastRow
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
Next
Next
End Sub
 
P

Per Jessen

Hi

This will give you 90 matches. Each team will play against each other twice
(as host and as guest).

Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "C"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow
For j = firstRow To lastRow
If Cells(i, datacolumn) <> Cells(j, datacolumn) Then
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
End If
Next
Next
End Sub

Regards,
Per
 
G

Gilbo

Now you have work out the fixtures, would it be possible to work out the
matches for each week with another macro,
ie 12 teams 6 matches
Team1 v Team2
Team3 v Team4
Team5 v Team6
Team7 v Team8
Team9 v Team10
Team11 v Team12
So that every team plays every other team twice, and all teams play every week
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top