Chris "Macro" Newman :) VBA loop

 I am trying to understand  this

How To Repeat Tasks With Your VBA Code by Looping

April 09, 2015 Chris Newman

Your Complete Looping Guide

Looping is extremely important when coding because it can shorten up the length of your code tremendously.  It's kind of like having a macro inside your macro since it allows you to repeat the same task (or apply the same action) over and over and over again.  Each looping statement has an opening line and a closing line; a beginning and an end if you will.  You will want to insert your repeatable action in between the opening and closing lines of the statement so your code keeps getting repeated. Let's start with the basics and cover the three major ways to loop.

For Loop

This type of loop is great for cycling through a pre-determined number of times.  The loop needs to begin with the word "For" and then a variable name followed by an equals sign (=) and finally the range from which you want your variable to equal during the loop (ie 15 to 25 or 1 to 5). The below example code will write a given phrase into the Immediate Window one hundred times.

Sub For_Loop()

Dim x As Long

'Write "I will not chew gum in class" 100 times
  For x = 1 To 100
    Debug.Print x & ". I will not chew gum in class."
  Next x

End Sub

For Each Loop

The For Each looping method is meant for cycling through objects on your spreadsheet.  This loop can handle anything from cells to charts. The key to this loop is making sure the loop knows which type of object to look for.  For example, if I want to loop through Pivot Tables in Sheet1, I'm going to want to tell the For Each loop to look "in" Worksheets("Sheet1").PivotTables. Most of the time, your object type is going to end in an "s". So you would NOT want to write Worksheets("Sheet1").PivotTable. This can be pretty tricky at first, so I've provided an example below and a TON of examples in the next section. Eventually, they will become second nature to you so don't give up on using them!

Sub ForEach_Loop()

Dim cell As Range

'How many cells in worksheet have values in them?
  For Each cell In ActiveSheet.UsedRange.Cells
    If cell.Value <> "" Then x = x + 1
  Next cell

'Report out results
  MsgBox "There are " & x & " cells containing data in you worksheet"

End Sub

Do While Loop

A Do While loop is probably the lesser known of the looping methods out there, but it can be very useful in certain situations.  Basically, the loop keeps cycling through until a logical test is failed (returns a false). In the below example, the Do While loop will keep on iterating until the variable BlankFound = True.

Sub DoWhile_Loop()

Dim BlankFound As Boolean
Dim x As Long

'Loop until a blank cell is found in Column A
  Do While BlankFound = False
    x = x + 1
  
    If Cells(x, "A").Value = "" Then
      BlankFound = True
    End If
  Loop

'Report out first blank cell found in Column A
  MsgBox "Cell A" & x & " is blank!"

End Sub

BE VERY CAREFUL! If your Do While loop never fails its test your loop will go on forever.  This may be good for a practical joke, but not for an automation macro. So make sure if you use this loop it is guaranteed to break its logical test at some point during your code's execution.

Loop Example VBA Code

My personal favorite method of looping would definitely be the For Each loop and because it can be a little confusing when you are first teaching yourself the awesome skill that is looping, I wanted to provide you with a ton of examples of different ways you can cycle through objects in your Excel files.  So without further ado, here is my "brain-dump" of loops to get you started!

** All the example code below is outputting names of the objects being looped into the Immediate Window (ie Debug.Print).  You can see what's being generated by displaying the Immediate Window with the shortcut Ctrl + g inside the Visual Basic Editor.

Looping Through Workbooks

Sub Workbook_Loop()

Dim wb As Workbook

'Loop through each open Excel Workbook
  For Each wb In Application.Workbooks
    Debug.Print wb.Name
  Next Workbook

End Sub

Looping Through Worksheets

Sub Worksheet_Loop()

Dim sht As Worksheet

'Loop through each worksheet in a workbook
  For Each sht In ThisWorkbook.Worksheets
    Debug.Print sht.Name
  Next sht

End Sub

Looping Through Cells

Sub Cell_Loop()

Dim cell As Range

'Loop through each cell in a cell range
  For Each cell In ActiveSheet.Range("A1:Z100")
    Debug.Print cell.Value
  Next cell

'Loop through each cell in a Named Range
  For Each cell In ActiveSheet.Range("RawData")
    Debug.Print cell.Value
  Next cell

'Loop through each cell in a Table body range
  For Each cell In ActiveSheet.ListObjects("Table1").DataBodyRange
    Debug.Print cell.Value
  Next cell

End Sub

Looping Through Charts

Sub Chart_Loop()

Dim sht As Worksheet
Dim cht As ChartObject

'Loop through each chart in the active workbook
  For Each sht In ActiveWorkbook.Worksheets
    For Each cht In sht.ChartObjects
      Debug.Print cht.Name
    Next cht
  Next sht

End Sub

Looping Through Shapes

Sub Shape_Loop()

Dim sht As Worksheet
Dim shp As Shape

'Loop through each shape in the active workbook
  For Each sht In ActiveWorkbook.Worksheets
    For Each shp In sht.Shapes
      If shp.Type = msoAutoShape Then
        Debug.Print shp.Name
      End If
    Next shp
  Next sht

End Sub

Looping Through Pivot Tables

Sub PivotTable_Loop()

Dim sht As Worksheet
Dim pvt As PivotTable

'Loop through each pivot table in the active workbook
  For Each sht In ActiveWorkbook.Worksheets
    For Each pvt In sht.PivotTables
      Debug.Print pvt.Name
    Next pvt
  Next sht

End Sub

Looping Through Tables

Sub Table_Loop()

Dim sht As Worksheet
Dim tbl As ListObject

'Loop through each table in the active workbook
  For Each sht In ActiveWorkbook.Worksheets
    For Each tbl In sht.ListObjects
      Debug.Print tbl.Name
    Next tbl
  Next sht

End Sub

Looping Through Array Items

Sub Array_Loop()

Dim myArray As Variant
Dim x As Long

'Fill your Array with data
  myArray = Array("OH", "GA", "FL", "TX")

'Loop through each item in an Array list
  For x = LBound(myArray) To UBound(myArray)
    Debug.Print myArray(x)
  Next x

End Sub

Looping Backwards Through Array Items

Sub Array_LoopBackwards()

Dim myArray As Variant
Dim x As Long

'Fill your Array with data
  myArray = Array("OH", "GA", "FL", "TX")

'Loop backwords through each item in an Array list
  For x = UBound(myArray) To LBound(myArray) Step -1
    Debug.Print myArray(x)
  Next x

End Sub

Looping Through Combo Boxes (Form Control

Sub ComboBox_FormControl_Loop()

Dim cmbo As Shape

'Loop through each Form Control Combo Box in ActiveSheet
  For Each cmbo In ActiveSheet.Shapes
    If cmbo.Type = msoFormControl And cmbo.FormControlType = xlDropDown Then
      Debug.Print cmbo.Name
    End If
  Next cmbo

End Sub

Looping Through Checkboxes (Form Control)

Sub CheckBox_FormControl_Loop()

Dim cb As Shape

'Loop through each Form Control Checkbox in ActiveSheet
  For Each cb In ActiveSheet.Shapes
    If cb.Type = msoFormControl And cb.FormControlType = xlCheckBox Then
      Debug.Print cb.Name
    End If
  Next cb

End Sub

Looping Through Option Buttons (Form Control)

Sub OptionButton_FormControl_Loop()

Dim OptBtn As Shape

'Loop through each Form Control Checkbox in ActiveSheet
  For Each OptBtn In ActiveSheet.Shapes
    If OptBtn.Type = msoFormControl And OptBtn.FormControlType = xlOptionButton Then
      Debug.Print OptBtn.Name
    End If
  Next OptBtn

End Sub

Looping Through Buttons (Form Control)

Sub Button_FormControl_Loop()

Dim btn As Shape

'Loop through each Form Control Button in ActiveSheet
  For Each btn In ActiveSheet.Shapes
    If btn.Type = msoFormControl And btn.FormControlType = xlButtonControl Then
      Debug.Print btn.Name
    End If
  Next btn

End Sub

Looping Through CheckBoxes (ActiveX)

Sub CheckBox_ActiveX_Loop()

Dim cb As OLEObject

'Loop through each ActiveX Control CheckBox in ActiveSheet
  For Each cb In ActiveSheet.OLEObjects
    If TypeName(cb.Object) = "CheckBox" Then
      Debug.Print cb.Name
    End If
  Next cb

End Sub

Looping Through Combo Boxes (ActiveX)

Sub ComboBox_ActiveX_Loop()

Dim cmbo As OLEObject

'Loop through each ActiveX Control Combo Box in ActiveSheet
  For Each cmbo In ActiveSheet.OLEObjects
    If TypeName(cmbo.Object) = "ComboBox" Then
      Debug.Print cmbo.Name
    End If
  Next cmbo

End Sub

Looping Through Option Buttons (ActiveX)

Sub OptionButton_ActiveX_Loop()

Dim optn As OLEObject

'Loop through each ActiveX Control Option Button in ActiveSheet
  For Each optn In ActiveSheet.OLEObjects
    If TypeName(optn.Object) = "OptionButton" Then
      Debug.Print optn.Name
    End If
  Next optn

End Sub

Looping Through Textboxes (ActiveX)

Sub TextBox_ActiveX_Loop()

Dim txtbx As OLEObject

'Loop through each ActiveX Control TextBox in ActiveSheet
  For Each txtbx In ActiveSheet.OLEObjects
    If TypeName(txtbx.Object) = "TextBox" Then
      Debug.Print txtbx.Name
    End If
  Next txtbx

End Sub

Looping Through Command Buttons (ActiveX)

Sub CommandButton_ActiveX_Loop()

Dim btn As OLEObject

'Loop through each ActiveX Control Command Button in ActiveSheet
  For Each btn In ActiveSheet.OLEObjects
    If TypeName(btn.Object) = "CommandButton" Then
      Debug.Print btn.Name
    End If
  Next btn

End Sub

Looping Through Toggle Buttons (ActiveX)

Sub ToggleButton_ActiveX_Loop()

Dim tggl As OLEObject

'Loop through each ActiveX Control Toggle Button in ActiveSheet
  For Each tggl In ActiveSheet.OLEObjects
    If TypeName(tggl.Object) = "ToggleButton" Then
      Debug.Print tggl.Name
    End If
  Next tggl

End Sub

More Looping Articles You Might Enjoy

• Loop Through All Excel Files In A Given Folder
• Loop An Input Box Until It Receives Correct or Valid Entry
• Break All External Links In Your Excel Workbook

Whew! That Was A Lot Of Examples!!

I warned you it was a brain dump! I'm sure you didn't need to absorb every example on this page, but hopefully I was able to get you to the loop you were trying to figure out.  If I missed something you were looking for, feel free to drop a note in the comments section and I'll see if I can help you with your looping question. Thanks for stoping by!

---

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)