Our next challenge is to 'dynamically' define a range to help us count the number of wins, losses and draws in a team's form.  What does 'dynamically' mean in this sense?  It means defining the range in such a way that it works for any team, home or away, on any row in the fixture data.  In other words, static ranges such as Range("A1:A2") simply won't work!

It's a real challenge in Excel VBA and one that might defeat an aspiring Excel developer.  Let's give ourselves the best chance of success by breaking it down and building it back up, step-by-step.

In the video, I take you through the process of conceptualising, building and testing this powerful dynamic mechanism.  There are no new techniques; rather, existing techniques are combined together in a new and even more powerful way.  The result?  The ability to count the number of wins, losses and draws in a team's recent form, with just a few lines of code.  It's a great example of dynamic position control that should find application in your other Excel analyses too.

Tools and Techniques

- .Cells referencing technique
- Range(range,range)
- String variable
- Testing with Msgbox
- COLUMN
- Application.WorksheetFunction
- COUNTIF