Get Started With Position Control In Excel VBA (Real-World VBA Task S3 P5)

I’ve already said in this series that a loop is the most powerful construct in Excel VBA. A close second, however, is position control. Why is position control so important in Excel VBA? Excel could be described as a ‘grid’. The worksheet is our workbench, our canvas, our playground, our medium of expression. It’s everything! That’s why the ability to navigate it effectively - to control ‘position’ - is so important. With this in mind, in this session, I introduce you to my favourite position control technique in Excel VBA - the Offset method.
💻DOWNLOAD THE EXCEL FILES
tinyurl.com/mr...
How to think about the offset method? Like many things in Excel, its name doesn’t describe its function intuitively. Think about an ‘offset’ as a ‘move away from’ a particular point. Three concepts are key here: a starting point or anchor, the number of rows to move away, and the number of columns to move away. These three concepts are expressed in Excel’s =OFFSET worksheet formula which facilities cool dynamic functions that we’ve covered many times on the channel.
But now we’re in VBA, of course, so how can we get offset working for us in the context of our task? In the video, I walk you through the mechanism step-by-step. We use offset to obtain an important piece of information: the result of a particular fixture (1, X, 2). As often happens, ticking off one task reveals another, leading us to the topic of the next video …
Thanks for following the Excel VBA Real-World Task, season 3! Leave any questions, queries or ideas in the RUclips comments. See you in part 6.
💻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 ...
💻EXCEL (VBA) FOR FOOTBALL TRADERS
tinyurl.com/y8...
💻MEMBERS’ MONDAY
tinyurl.com/2p...
💻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 RUclips comments how you get on.
💻SEASON 3 PLAYLIST
tinyurl.com/mp...
💻REAL-WORLD VBA SEASON 1
tinyurl.com/y4...

[@TigerSpreadsheetSolutions]

💻VBA REAL-WORLD TASK SEASON 3 PLAYLIST
tinyurl.com/mpsua8jh

[@sjorshoevenaars6150]

You're probably right about the lack of the condition, but in your first try, you put your name in the wrong column which would never give Chris as result

[@TigerSpreadsheetSolutions]

This is quite possible ...