Creating a fixture list
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 |
Creating a fixture list
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 "Gilbo" skrev i meddelelsen ... 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 |
Creating a fixture list
Thanks, It does exactly what I want.
"Gilbo" wrote: 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 |
Creating a fixture list
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 "Gilbo" wrote: Thanks, It does exactly what I want. "Gilbo" wrote: 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 |
All times are GMT +1. The time now is 10:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com