How To Combine Two Loop Types In Excel VBA (Real-World VBA Task S3 P4)
If you’re an Excel VBA Beginner, how many loop types do you need to know in Excel VBA? Well, for the most part, just one – a For Each loop. However, in very specific situations, other loop types can really help. We encounter one such situation in video 4: we need to loop through a dataset from bottom to top, rather than top to bottom, in order to look at the most recent fixtures first. As I explain in the video, it’s problematic with a For Each loop, so what are our options?
.jpg)
CLICK HERE TO WATCH THE VIDEO
You may have heard me say on the channel, ‘Be a value creator, not a technique collector’. I believe you should build your Excel VBA vocabulary on a ‘need-to-learn’ basis; in other words, you only learn a new technique as and when you need it. This avoids technique ‘obsession’ (learning techniques for their own sake) and moves the focus from theory to real-world value. You want to use Excel VBA to improve people’s lives, right? That’s my Excel ‘success’.
You may have heard me say on the channel, ‘Be a value creator, not a technique collector’. I believe you should build your Excel VBA vocabulary on a ‘need-to-learn’ basis; in other words, you only learn a new technique as and when you need it. This avoids technique ‘obsession’ (learning techniques for their own sake) and moves the focus from theory to real-world value. You want to use Excel VBA to improve people’s lives, right? That’s my Excel ‘success’.
The video illustrates how For Next opens up new possibilities justifying, in my view, its addition to your Excel VBA toolkit. Building the mechanism step-by-step, I show how For Next offers a higher level of control and flexibility. In fact, it’s exactly the tool we need to get this job done. Onwards!
That’s video 4 of 7 complete. Are you following the Excel VBA Real-World Task series, season 3? I would love to hear from you in the YouTube comments. More powerful Excel VBA techniques to come in video 5!
ABOUT OUR MEMBER COMMUNITIES
Our learning communities provide exclusive Excel learning content, direct access to Chris, support and inspiration from others just like you and much, much more. They are the best way to develop your Excel skills long-term, and to make a few friends along the way! Check out the options below, we would LOVE to welcome you ...
ABOUT REAL-WORLD VBA TASKS SEASON 3
In the Real-World VBA Task series, we tackle a VBA challenge similar to the jobs that professionals you like are trying to automate daily using Excel VBA.
In Season 3 of the Excel VBA Real-World Task series, we explore a job I’ve been tasked with many times in my career: creating form analysis for football teams in a league table. Form analysis the kind of thing you’ll see alongside football league tables in the newspaper or online, and it’s the perfect topic for this series for two reasons. First, it’s a task that’s not easily done in Excel without using VBA (though it’s possible using a series of very long formulae). Second, it requires powerful techniques such as loops, conditional statements, position control and more – so it’s broadly applicable to VBA tasks generally. In other words, the series should help whatever VBA job you’re currently wrestling with.
In this 7-part season, I walk you through the VBA task step-by-step, from the critical planning and conceptualization phase, to application of loops, variables and more in VBA, through to combining techniques together to create a powerful ‘click-of-button’ solution. Even I was surprised by how quickly this macro gets the job done …
It’s about more than impressing you with individual techniques such as loops in Excel VBA, however. It’s about learning an overall approach to VBA development that you can apply in your work. Look out for the Excel ‘Metaskills’ such as debugging that I showcase during the series – they really are the hidden gems in Excel learning. I hope enjoy the series and do let me know in the YouTube comments how you get on.