How to Use Excel VBA Code - Real Example P3

How can we use Excel VBA code to speed up manual work in Excel? In this series we explore an example Excel VBA project sent in by Eric, a viewer of the channel. We aim to build an Excel VBA application to move data around a file at the 'click-of-a-button', saving hours of frustrating manual work. The application should be flexible and scaleable enough to handle changes in business circumstances - a challenge indeed! Let's get into it...

Download File Link:
tinyurl.com/y4d6ds62
----------------------------------------------------------------
In the third video, we first implement a 'loop within a loop' - the powerful mechanism that allows us to get the first part of this job done. Conceptual thinking always comes first, so what exactly are we trying to do, and can we explain it clearly in normal language? Let's try...

We already have one loop set up which loops through the worksheets in the file. The next loop, which operates 'one level down' and is embedded within the first, will loop through the name cells on each sheet. With the conceptual understanding clear, we can move onto the programming.

Chris uses a 'for each object in the collection' / 'for - each' loop to work through the name cells on each sheet. This type of loop could be applied to open workbooks, shapes on a sheet, sheets in a file, or another of Excel's 'collections' of objects. It shows how, if we can navigate Excel's object hierarchy, we can make coding easier.

In this case we loop through each cell in the range of cells that contains customer names, as defined by the dynamic range reference we created in the previous video. We test the mechanism using message boxes to extract information from the VBA editor during code execution. Job done - next!

The next step is to understand if a particular name appears in the list of names we are assembling. Remember, Eric has requested a list of 'unique' names, so names must not be duplicated. So, how can we check if a name already appears in the list? We set up a thing of beauty: worksheet formulae working together with Excel VBA to get a job done. In this case we create a 'COUNTIF' formula to establish if a value appears in a range; if the formula returns a value of zero, we know the name needs to be added to the list.

Onwards!

See you in part 4. Don't forget to let us know in the comments how you get on, Chris will get back to you.

Series Playlist Link:
tinyurl.com/y4pmrkxn

🎁YOUR EXCEL CHEATSHEET
🎁1 Page Summary Of What You Need To Know
tinyurl.com/yc7jlmbs

facebook.com/TigerSpreadsheetSolutions
twitter.com/TigSpreadsheets

Also on Insta :-)