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.
If you're unfamiliar with Loops in VBA, you should review the Loops Chapter in the VBA Tutorial.
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.
The Loop Builder can generate code to loop through the following items:
Each type of loop has it's own unique settings:
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:
Loops through all sheets in the workbook.
Loops through all open workbooks.
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).
Loops through numbers. Define a start number, an end number, and the increment by which to loop (default = 1).
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.
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).
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.
Last modified 6 months ago.