Table of Contents

Possible Football Matches

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:

Possible Football Matches in Excel VBA

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(“A1”).CurrentRegion
counter = 0

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

Worksheets(1).Columns(3) = “”

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 & ” vs ” & 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:

Possible Football Matches Result

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

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog