How to create script for a Google Sheet from a Microsoft Macro making a game scheduler

Viewed 25

I am going to include the Macro from Excel and see if anyone can help with something for Google Sheets script. This script makes a game schedule based on entering of 2 things: Enter number of games and number of teams.

Once entered it creates match ups making sure column A (home games) and Column B (away games) are equal. And that every team gets the alotted games vs opponents preferably every other possible opponent first!

I accept this might be impossible. I am open to other approaches to make this simpler.

Sub gameplan()
Dim TEAM(10000) As Single
Dim H(10000) As Single
Dim A(10000) As Single
  Range("A3:B65536").ClearContents
  MY_TEAMS = Range("A1").Value
  MY_GAMES = Range("A2").Value
  MY_COUNT = 1
  MY_TEAM = 1
  MY_TEAM_2 = 2
 Do Until MY_TEAM >= MY_TEAMS And MY_TEAM_2 >= MY_TEAMS
     If H(MY_TEAM) < MY_GAMES / 2 Then 'A(MY_TEAM) Then
  MY_COL = 0
  MY_ROW_2 = 0
  MY_COL_2 = 1
Else
  MY_COL = 1
  MY_ROW_2 = 1
  MY_COL_2 = 0
End If
  Range("a65536").End(xlUp).Offset(1, MY_COL).Value = "Team " & MY_TEAM
  Range("a65536").End(xlUp).Offset(MY_ROW_2, MY_COL_2).Value = "Team " & MY_TEAM_2
If MY_COL = 0 Then
  H(MY_TEAM) = H(MY_TEAM) + 1
  A(MY_TEAM_2) = A(MY_TEAM_2) + 1
Else
  H(MY_TEAM_2) = H(MY_TEAM_2) + 1
  A(MY_TEAM) = A(MY_TEAM) + 1
End If
  MY_COUNT = MY_COUNT + 1
  TEAM(MY_TEAM) = TEAM(MY_TEAM) + 1
  TEAM(MY_TEAM_2) = TEAM(MY_TEAM_2) + 1
If TEAM(MY_TEAM) = MY_GAMES Then
  MY_TEAM = MY_TEAM + 1
End If
  If TEAM(MY_TEAM_2) = MY_GAMES Then
   MY_TEAM_2 = MY_TEAM + 1
End If
   MY_TEAM_2 = MY_TEAM_2 + 1
  If MY_TEAM_2 > MY_TEAMS Then
   MY_TEAM_2 = MY_TEAM + 1
  End If
 Loop
End Sub
0 Answers
Related