How to Use Possible Football Matches In Excel

Oct 18, 2020 • edited Oct 21, 2020

Below we will look at a program in Excel VBA that shows a print preview of all the possible football matches from a list of teams.

Situation:

1. First, we declare one Range object and four variables. We call the Range object rng. One String variable we call matchname, and three Integer variables we call counter, i and j.

``````          Dim rng As Range, matchname As String, counter As Integer, i As Integer, j As Integer
``````

2. We initialize rng with the team names. We use CurrentRegion because we don't know the exact boundaries of the range in advance (we want this program to work for 3 teams but also for 12 teams). We initialize counter with value 0.

``````          Set rng = Range(&quot;A1&quot;).CurrentRegion

counter = 0
``````

3. We write all the possible football matches to column C. First, we empty column C.

``````          Worksheets(1).Columns(3) = &quot;&quot;
``````

4. We start a Double Loop.

``````          For i = 1 To rng.Count

For j = i + 1 To rng.Count
``````

5. We write a matchname to the variable matchname.

``````          matchname = rng.Cells(i).Value &amp; &quot; vs &quot; &amp; rng.Cells(j).Value
``````

For example, for i = 1 and j = 2, Excel VBA writes the matchname Kickers vs Shooters. For i = 1 and j = 3, Excel VBA writes the matchname Kickers vs Little Giants, etc.

6. We write the matchname to column C.

``````          Cells(counter + 1, 3).Value = matchname
``````

7. The counter keeps track of the number of matchnames written to column C. Excel VBA increments counter by 1 each time it writes a matchname to column C. To achieve this, add the following code line:

``````          counter = counter + 1
``````

8. Don't forget to close the two loops.

``````          Next j

Next i
``````

9. We show a print preview of all the possible football matches.

``````          ActiveSheet.Columns(3).PrintPreview
``````

10. Test the program.

Part of the result:

Note: column C is manually centered to get this result.

#Tutorial#How To#VBA#Range Object

How To Use Option Explicit In Excel

How To Use Randomly Sort Data In Excel