Loop Builder

What does it do?

The Loop Builder generates code to loop through objects (ex. cells, sheets, or shapes), arrays, or numbers, performing action(s) on some or all of the items in the loop.

How to Access it?

VBA: Intro to Loops

Loops allow you to perform the same action on multiple items without re-writing the code to perform the action:

Dim Cell as Range
For Each Cell in Range("a1:a10")
    if cell.value = FALSE then cell.offset(0,1).value = 0
Next Cell

Dim i as integer
For i = 1 to 10
    if range("a" & i).value = FALSE then range("b" & i).value = 0
next i

Both of these examples will loop through cells A1:A10, checking to see if the value = FALSE, and if so, output 0 in column B.

Loop Builder Walkthrough

Loop Through

The Loop Builder can generate code to loop through the following items:

• Cells
• Sheets
• Workbooks
• Shapes
• Charts
• PivotTables
• Tables
• Numbers
• Array Items

Each type of loop has it's own unique settings:

Cells

Loops through all cells in a range.

Define Range - Define the range to loop through (ex. A1:A100)

Define Sheet - Define the sheet where the loop range is located Three Options:

1. Activesheet - Uses the ActiveSheet
   1. 
2. All Sheets in Workbook - Loops through all sheets in a workbook
   1. 
3. Specific Sheets - Specify a sheet name
   1. 

Sheets

Loops through all sheets in the workbook.

Workbooks

Loops through all open workbooks.

Shapes, Charts, PivotTables, and Tables

Loops through all objects of the specified type in a worksheet or all worksheets in a workbook.

Define Sheet - Define the sheet where the objects are located (same options as 'Cells' above).

Numbers

Loops through numbers. Define a start number, an end number, and the increment by which to loop (default = 1).

• Start Number The first number
• End Number The last number
• Step The increment.
  • "1" will loop through the numbers one at a time (1,2,3, etc.). "2" skips a number (1,3,5, etc.). Negative numbers (make sure to define your start and end numbers properly) and decimal values are allowed.

Array Items

Loops through items in an array.

Arrays are groups of items defined within VBA. These items can be numbers, text, objects, etc.

Array Items does not have any additional settings.

Actions

Defines the action(s) to be performed on the items in the loop. There are too many actions to cover each one individually. Hopefully they are self-explanatory! If not, a simple online search should tell you what you need to know.

Below the "Actions" box, are two checkboxes:

No Criteria? - Determines if actions are performed on all items in the loop or only on items meeting certain criteria. This is checked by default, once unchecked, the Criteria section will appear below.

Match Action with Criteria - When unchecked (default), each action is performed on ALL items that meet ALL criteria. When unchecked, each action corresponds to it's corresponding criteria below (Action 1 is linked to Criteria 1). So if Criteria 1 is met, then action 1 is performed. If Criteria 1 is not met, then Criteria 2 is checked, etc.

Note: When "Match Action with Criteria" is checked, if an item meets multiple criteria, only the action from the first criteria match will be performed. In other words, if an item matches criteria 1, the code will not check if the item matches criteria 2 or criteria 3. This is because the If Statement uuses an ELSEIF condiion. Instead, if you desire that not desired, you will need to manually adjust the code (create separate IF statements, instead of one large If block).

Criteria

Note: This is only visible when "No Criteria?" is unchecked above.

Here define the criteria that determine whether to perform the Action(s) above. These criteria allow wildcards (?, *) to increase criteria flexibility.