How to Continue Excel VBA For Loop (with Examples)

 

How to Launch a VBA Editor in Excel

  • Click on the Developer Tab from Excel Ribbon. In case you don’t have the Developer Tab in Excel Ribbon, follow this article about how to add Developer Tab in Excel Ribbon.
  • Select the Visual Basic option.

Visual Basic Option of Developer Tab

  • Excel will lead you to the VBA Editor Window. Click on Insert and select Module.

Inserting Module to Write Code

  • In this editor, you can write your VBA code.

VBA editor window


Overview of the Excel VBA For Loop Statement

Syntax:

For counter = Start to End [Step]
Statements
Next counter

Example: Use For Loop to Get Even Numbers from 1 to 30

Here is a simple example of how you can use the For loop to get the Even Numbers from 1 to 30.

Overview image of Using For Loop to Get Even Numbers

Sub Get_Even_Numbers()
Dim i As Integer
For i = 1 To 30
    If i Mod 2 = 0 Then
        Debug.Print i
    Else
    End If
Next i
End Sub
  • Use this code in your VBA editor and Run the code. You will get even numbers from 1 to 30.

Code Breakdown

Sub Get_Even_Numbers()

The code defines a subroutine named “Get_Even_Numbers”.

Dim i As Integer

It declares a variable named “i” as an Integer.

For i = 1 To 3 

It sets up a loop that will iterate from 1 to 30, with the loop index variable “i” taking on each value in that range.

 If i Mod 2 = 0 Then
        Debug.Print i
    Else
  End If

For each value of “i” in the loop, the code checks if “i” is an even number by using the “Mod” operator to check if the remainder when “i” is divided by 2 is equal to 0. If “i” is even, it prints the value of “i” to the Debug window. If “i” is odd, the code does nothing.

Next i
End Sub

Once the loop has finished iterating through all values of “i” from 1 to 30, the subroutine ends.

Output of VBA code to get Even Numbers


How to Continue the Excel VBA For Loop Statement: 2 Examples

Since VBA doesn’t have a continue statement to skip an iteration, you can use other statements within a For loop.


Example 1 – Use the If Statement to Skip an Iteration and Continue to Other Iterations

Suppose you have a dataset of the marks of some students. Among the students, some were Absent during the examination. You want to get the present students’ ID, Name, and Marks. You can perform this task by simply using a For…Next Loop. But, as the Value of Marks column is Absent in 2 cells, the Iteration of For Loop may face some difficulty continuing the loop while i=9.

Overview image to Continue For Loop

  • Use the code below in the VBA Editor.
  • Press Run.
  • You get the ID, Name, and Marks of the Present Students on the Immediate window.
Sub For_loop_continue_use_if()
    Dim i As Integer
    Dim lastrow As Integer
    ' Find the last row in the data set
    lastrow = Range("D" & Rows.count).End(xlUp).row
    ' Loop through each row in the data set
    For i = 5 To lastrow
        ' Perform some operation on each row
        If cells(i, 4).Value = "Absent" Then
        'Do nothing
        ' For example, you can print the ID, Name, and Age of each person
        Else
        Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
        cells(i, 3) & ", Marks: " & cells(i, 4)
        End If
    Next i
End Sub

Code Breakdown

Sub For_loop_continue_use_if()

This line starts the definition of the subroutine and sets its name.

Dim i As Integer
Dim lastrow As Integer

These 2 lines declare 2 variables as integers: i and lastrow. Here, “i” is used as a loop counter, and “lastrow” is used to store the last row number of the dataset.

lastrow = Range("D" & Rows.count).End(xlUp).row

This line finds the last row number of the dataset. It starts from the bottom of the worksheet column D where is the Marks data “Range(“D” & Rows.count)” and goes up until it finds the last non-empty cell “End(xlUp)”. Finally, it returns the row number of that cell “.row”.

For i = 5 To lastrow

In this step, a loop starts that will iterate through all rows from row 5 to the last row of the dataset.

If cells(i, 4).Value = "Absent" Then

This line checks the value of the cell in the fourth column of the current row “cellls(i,4).value”. If it is equal to the string “Absent”, then nothing happens.

 Else
        Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
        cells(i, 3) & ", Marks: " & cells(i, 4)

This step is executed if the value of the 4th column of the current row is not “Absent”. In this case, the subroutine prints a message to the immediate window that shows 2nd, 3rd, and 4th columns of the current row. This message is formatted as “ID:(value of 2nd column) , Name: (value of 3rd column), Marks: (value of 4th column)”.

 End If
    Next i

This step marks the end of the If statement and the end of the For loop. It increments i by 1 and repeats the loop until it reaches the last row of the dataset.


Example 2 – Apply On Error Resume Next Within a For Loop to Skip an Iteration If Any Cell Has an Error Value

During performing iterations, if the For loop faces an error value, the code doesn’t work. For example, we have a dataset where 2 of the cells have a #DIV/01 error. The loop will skip such error values and continue the rest of the iterations.

  • Use the following code on your VBA editor.
  • Run the code by pressing Alt + F5 to see the result.

VBA code to Continue For loop by applying On Error Resume Next

Sub For_loop_continue_on_error()
    Dim i As Integer
    Dim lastrow As Integer
     ' Find the last row in the data set
    lastrow = Range("D" & Rows.count).End(xlUp).row
    ' Loop through each row in the data set
    For i = 5 To lastrow
    On Error Resume Next
        Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
        cells(i, 3) & ", Marks: " & cells(i, 4)
    Next i
End Sub

Code Breakdown

Sub For_loop_continue_on_error()

This line defines the subroutine named “For_loop_continue_on_error()”

Dim i As Integer
            Dim lastrow As Integer

These two lines of the code declare 2 variables i and lastrow.

lastrow = Range("D" & Rows.count).End(xlUp).row

This line finds the last row number of the dataset. It starts from the bottom of the worksheet column D where is the Marks data “Range(“D” & Rows.count)” and goes up until it finds the last non-empty cell “End(xlUp)”. Finally, it returns the row number of that cell “.row”.

For i = 5 To lastrow
On Error Resume Next
        Debug.Print "ID: " & cells(i, 2) & ", Name: " & _
        cells(i, 3) & ", Marks: " & cells(i, 4)
             Next i

This block of code creates a For loop that iterates through the rows of data, starting from row 5 and ending at the last row. Within this loop, the “On Error Resume Next” statement in Excel VBA tells VBA to ignore any errors that occur in the subsequent code and continue with the next iteration of the loop. The “Debug.Print” statement prints a message to the intermediate window that includes the ID, Name, and Marks of the dataset. The underscore character at the end of the second line allows the statement to continue on the next line for readability.

This code loops through a range of data, ignoring errors and continuing to the next iteration of the loop, and prints a message to the intermediate window for each row of data.


Some Cases Where You Can Continue Iterations in an Excel VBA For Loop

Case 1 – Use If Statement Within a For Loop to Create Criteria Until the Loop May Continue


Part 1.1 Single Criteria in For Loop

If you want to skip a specific iteration and then continue the loop, you can follow this code.

Overview image to omit single iteration

Sub omit_single_iteration()
Dim i As Integer
Dim output As Variant
For i = 1 To 10 Step 1
    If i = 6 Then
     'Do Nothing
    Else
    output = output & vbNewLine & i
    End If
Next i
Debug.Print output
End Sub

Code Breakdown

This code block is written in Visual Basic for Applications (VBA) and uses a loop to generate a string containing numbers from 1 to 10, skipping 6. Here’s a Code Breakdown of what’s happening.

Dim i As Integer

This line declares a variable named i as an Integer.

Dim output As Variant

This Portion declares the output variable as a Variant.

For i = 1 To 10 Step 1

This step sets up a loop that will run 10 times, with i starting at 1, incrementing by 1 each time, and stopping at 10.

If i = 6 Then

It checks if the value of i is equal to 6.

'Do Nothing

If i is equal to 6, the code block between this line and the End If statement will not execute. In other words, nothing will happen for i = 6.

Else

If i is not equal to 6, the code block following this line will execute.

output = output & vbNewLine & i

This line appends a new line character (vbNewLine) and the current value of i to the output string variable.

End If

This step ends the If statement.

Next i

This line signals the end of the loop and moves the loop counter (i) to the next value.

Debug.Print output

Finally, this line prints the value of the output variable to the immediate window in the VBA editor.

In summary, the code generates a string with numbers 1 to 10 (excluding 6) and prints the resulting string to the immediate window for debugging purposes.

Output of skip an iteration and continue For Loop

Read More: Excel VBA For Loop with Array


Part 1.2 – Multiple Criteria Within If Statements and Or Operator

This is an example where we skipped the iteration for 3 iterations and continued the For loop for the other iterations.

Overview image to Omit Multiple Iterations

Sub omit_multiple_iterations()
Dim i As Integer
Dim output as String
For i = 1 To 10 Step 1
    'Omit multiple iterations
    If i = 6 Or i = 8 Or i = 9 Then
     'Do Nothing
    Else
    output = output & vbNewLine & i
    End If
Next i
Debug.Print output
End Sub

Code Breakdown

This VBA code defines a subroutine called “omit_multiple_iterations”. Here’s a Code Breakdown of the code:

Dim i As Integer
Dim output As String

This portion of the code declares 2 variables: i, output as integer and string respectively. i is used for iterating through the loop. output is to store the result.

For i = 1 To 10 Step 1

The loop starts with the For statement, which initializes the value of i to 1 and sets the loop to run while i is less than or equal to 10. The Step 1 argument specifies that the loop should increment i by 1 each time.

 If i = 6 Or i = 8 Or i = 9 Then

The If statement checks whether i is equal to 6, 8, or 9. If it is, then the code does nothing, and the loop moves on to the next iteration. If i is not equal to any of those values, the code moves on to the Else clause.

In the Else clause, the code appends the value of i to the output variable. The & operator concatenates the previous value of output with a new line character and the current value of i. The result is that each value of i that is not equal to 6, 8, or 9 is added to the output variable on a new line.

After the loop has finished iterating, the code prints the value of output to the Debug window using the Debug. Print statement.

In summary, this code skips over iterations where i is 6, 8, or 9 and concatenates the remaining values of i to a string with a line break. The final output is printed to the Debug window.

Final output of skipping Multiple iterations


Case 2 – Skipping Values in an Array with the For Each Loop

In this code, we have a list of 5 different items. We want to print only Cheetos, Cheese, and Milk. We have used the If statement to skip iterations of other values such as Barcel and Yogurt.

Overview image to use For Each Statement to Apply Command for Each Value of Array

Sub for_loop_continue_array()
   Dim Groceries(1 To 5) As String
   Dim concat_str As String
   Dim item As Variant 
   Groceries(1) = "Cheetos"
   Groceries(2) = "Barcel"
   Groceries(3) = "Cheese"
   Groceries(4) = "Yogurt"
   Groceries(5) = "Milk"
   'Omit values like continue does
   For Each item In Groceries
       If item = "Barcel" Or item = "Yogurt" Then
        'Do Nothing
       Else
        concat_str = concat_str & item & ", "
       End If
   Next item
   Debug.Print concat_str
End Sub

Code Breakdown

This VBA code defines a subroutine named for_loop_continue_array that performs the following actions:

Dim Groceries(1 To 5) As String

Declares an array named “Groceries” of size 5, where each element is a string. The array is populated with 5 grocery items: “Cheetos”, “Barcel”, “Cheese”, “Yogurt”, and “Milk”.

Dim concat_ str As String

Declares a string variable named “concat_str” that will be used to concatenate all the grocery items except “Barcel” and “Yogurt”.

Dim item As Variant

Declares a variant variable named “item”.

For Each item In Groceries

Loops through each item in the “Groceries” array using a “For Each” loop.

If item = "Barcel" Or item = "Yogurt" Then
        'Do Nothing
       Else
        concat_str = concat_str & item & ", "
      End If

For each item, it checks if it is equal to “Barcel” or “Yogurt”. If it is, then nothing happens and the loop continues to the next item. If it is not, then the item is concatenated to the “concat_str” string variable followed by a comma and a space.

Debug.Print concat_str

After all items have been checked, the concatenated string variable “concat_str” is printed to the debug console using the “Debug.Print” statement.

The purpose of the code is to concatenate all the grocery items in the “Groceries” array except for “Barcel” and “Yogurt” into a string, which is then printed to the debug console. The code achieves this by using a “For Each” loop and an “If” statement with a “continue” behavior to skip over the unwanted items.

Final output of using For Each Statement to Apply Command for Each Value of Array

Read More: Excel VBA: For Loop Skip to Next Iteration


How to Continue Iterations in Other Loops in Excel VBA


Case 1 – Continue Do Until Loop If Any Cell Has Negative value

We have a dataset where a Student gets negative marks in Literature. But, we want to calculate the total marks of the students without subtracting the negative marks from the total marks. We have skipped an iteration of Do Until loop which adds the negative marks.

Sub Do_until_continue_loop()
    Dim ws As Worksheet
    Dim total As Double
    Dim i As Integer
      ' Set the worksheet object to the active sheet
    Set ws = ActiveSheet
    i = 5
    ' Loop through the values in column A
    Do Until IsEmpty(ws.cells(i, 3))
        ' Check if the value is negative
        If ws.cells(i, 3) < 0 Then
            ' Skip negative values
            i = i + 1
        Else
       ' Add the value to the total
        total = total + ws.cells(i, 3)
        i = i + 1
        End If
    Loop
    ' Display the total in a message box
    cells(11, 3).Value = total
End Sub

Code Breakdown

Sub Do_until_continue_loop()

The code defines a subroutine called “Do_until_continue_loop”.

Dim ws As Worksheet
  Dim total As Double
  Dim i As Integer

It declares three variables: “ws” as a worksheet object, “total” as a double-precision floating-point number, and “i” as an integer.

Set ws = ActiveSheet

The variable “ws” is set to the active worksheet using the “ActiveSheet” property.

i = 5

The variable “i” is initialized to 5.

Do Until IsEmpty(ws.cells(i, 3))

The code uses a “Do Until” loop to iterate through the cells in column C, starting from row 5, until an empty cell is encountered.

 If ws.cells(i, 3) < 0 Then
  i = i + 1

Inside the loop, the code checks if the current cell value is less than 0. If it is, it increments the “i” variable to skip the current row.

 Else
      total = total + ws.cells(i, 3)
      i = i + 1
   End If

If the current cell value is greater than or equal to 0, the code adds the current cell value to the “total” variable and increments the “i” variable.

cells(11, 3).Value = total
End Sub

Once the loop finishes, the code writes the value of the “total” variable to cell C11.

Final output of Continue Do Until Loop


Case 2 – Continue Do While Loop If Any Cell Value is Negative

For the same dataset, we will use the Do While loop.

Sub CalculateTotal()
    Dim ws As Worksheet
    Dim total As Double
    Dim i As Integer
      ' Set the worksheet object to the active sheet
    Set ws = ActiveSheet
    i = 5
    ' Loop through the values in column A
    Do While Not IsEmpty(ws.cells(i, 3))
        ' Check if the value is negative
        If ws.cells(i, 3) < 0 Then
            ' Skip negative values
            i = i + 1
        Else
       ' Add the value to the total
        total = total + ws.cells(i, 3)
        i = i + 1
        End If
    Loop
    ' Display the total in a message box
    cells(11, 3).Value = total
End Sub

Code Breakdown

Dim ws As Worksheet

Declares a variable named ws of type Worksheet.

 Dim total As Double

Declare a variable named total of type Double.

 Dim i As Integer 

Declare a variable named i of type Integer and set it to 5.

Set ws = ActiveSheet
    i = 5

Set the ws object to the active worksheet.

Do While Not IsEmpty(ws.cells(i, 3))

Begin a loop that continues until the cell in column C at the current row is empty.

 If ws.cells(i, 3) < 0 Then

Check if the value in column C at the current row is negative. If it is, skip to the next row.

Else
  total = total + ws.cells(i, 3)

If the value in column C at the current row is non-negative, add it to the total.

 i = i + 1
End If

Increment i by 1 to move to the next row.

 cells(11, 3).Value = total

Display the value of the total in cell C11.

Final output of Calculating total marks using continue Do while loop


How to Exit a For Loop in Excel VBA


Method 1 – Use the Exit For Statement to Stop Before the Mentioned Iteration

Exit For Statement works great to exit a loop.

  • Follow the code mentioned below.

Overview image of Exiting for loop

Sub for_loop_exit()
For i = 1 To 10
    If i = 5 Then
        Exit For
    End If
    Debug.Print i
Next i
End Sub

Code Breakdown

Sub for_loop_exit()

The code defines a subroutine named “For_loop_exit”.

For i = 1 To 10

The loop is defined using a “For” statement with a counter variable “i” that starts at 1 and goes up to 10.

If i = 5 Then
        Exit For
    End If
               Debug.Print i
           Next i
           End Sub

Within the loop, an “If” statement checks whether the current value of “i” is equal to 5. If “i” is equal to 5, the “Exit For” statement is executed, which immediately exits the loop and jumps to the next statement after the “Next i” statement. If “i” is not equal to 5, the code inside the loop is executed, which in this case simply prints the value of “i” to the immediate window. Once the loop has been completed, the subroutine ends.

Read More: Excel VBA: How to Exit a For Loop


Method 2 – Apply the GoTo Statement to Stop a For Loop After a Specific Iteration

The VBA “goto” statement is a control structure used to transfer control to a specific line or label within a VBA procedure. It is often used to create more complex flow control structures or to handle errors and exceptions.

Overview image of using goto statement to exit for loop

Sub jump_using_goto()
Dim i As Integer
For i = 1 To 10
    If i = 5 Then
        GoTo Line1
    End If
Debug.Print "The value is " & i
Next i
Line1:
End Sub

Code Breakdown

  • Sub jump_using_goto()
    Dim i As Integer

The subroutine starts with a variable declaration i as an integer.

  • For i = 1 To 10

A For loop is used to iterate from 1 to 10, and for each iteration, the value of i is printed to the debug window using the Debug.Print statement.

  • If i = 5 Then
            GoTo Line1
        End If
    Debug.Print "The value is " & i
    Next i
    Line1:
    End Sub

The If statement checks whether the current value of i is equal to 5. If it is, then the program jumps to the Line1 label using the GoTo statement. If i is not equal to 5, then the program continues to execute the Debug.Print statement. The Line1 label is defined at the end of the subroutine and serves as a target for the GoTo statement. When the program reaches the Line1 label, it continues executing from that point until the end of the subroutine.


Method 3 – Calculate Sales Data Using the For Loop Continue of Excel VBA

This is a dataset of Sales of a company. To calculate the total sales, we have used For loop. But, some products were not available to purchase. When the For loop enters into such a counter, it can’t do the mathematical operation and an error occurs. If the value of the Quantity column is “Not Available,” the loop skips this cell and enters the next cell.

  • Use the following code:

VBA code of using for loop continue to get sales data

Sub For_loop_example1_sales()
    Dim lastrow As Long
    Dim i As Long
    Dim quantity As Double
    Dim price As Double
    Dim totalSales As Double
    lastrow = ActiveSheet.ListObjects("Table1").Range.Rows.count
    For i = 2 To lastrow
        If ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value = "Not Available" Then
        Else
        quantity = ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value
        price = ActiveSheet.ListObjects("Table1").Range.cells(i, 3).Value
        totalSales = quantity * price
        ActiveSheet.ListObjects("Table1").Range.cells(i, 4).Value = totalSales
        End If
    Next i
End Sub

Code Breakdown

This VBA code calculates the total sales for each row in a table named “Table1” and stores the result in a new column.

Here is a Code Breakdown of the code:

Dim lastrow As Long 

This line declares a variable named “lastrow” as a Long data type. This variable will be used to store the last row number of the “Table1” table.

Dim i As Long 

This portion declares a variable named “i” as a Long data type. This variable will be used as a loop counter to iterate through each row in the “Table1” table.

Dim quantity As Double  

It declares a variable named “quantity” as a Double data type. This variable will be used to store the quantity value from each row in the “Table1” table.

Dim price As Double 

This line declares a variable named “price” as a Double data type. This variable will be used to store the price value from each row in the “Table1” table.

Dim totalSales As Double -

Here, this part of the code declares a variable named “totalSales” as a Double data type. This variable will be used to store the result of the quantity multiplied by price calculation.

lastrow = ActiveSheet.ListObjects("Table1").Range.Rows.count 

This step retrieves the total number of rows in the “Table1” table and assigns it to the “lastrow” variable.

For i = 2 To lastrow 

It starts a loop that will iterate through each row in the “Table1” table, starting from row 2 and ending at the “lastrow” value.

If ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value = "Not Available" Then

This line checks if the value in column 2 (quantity) for the current row is “Not Available”.

quantity = ActiveSheet.ListObjects("Table1").Range.cells(i, 2).Value 

If the value in column 2 is not “Not Available”, assign the value in column 2 to the “quantity” variable.

price = ActiveSheet.ListObjects("Table1").Range.cells(i, 3).Value 

This step assigns the value in column 3 (price) to the “price” variable.

totalSales = quantity * price 

It calculates the total sales for the current row by multiplying the “quantity” and “price” variables.

ActiveSheet.ListObjects("Table1").Range.cells(i, 4).Value = totalSales 

This part of the code stores the calculated total sales value in a new column (column 4) in the current row.

End If 

It ends the If statement.

Next i 

Moves to the next row in the “Table1” table.

final output of using for loop continue to get sales data


Difference Between For and For Each Loops in Excel VBA

The main difference between the two loops is the way they iterate through a collection or array.

The For loop is used to iterate through a collection or array by index. It has the following syntax:

For counter = start To end [Step stepvalue]
    [statements to be executed inside the loop]
Next [counter]

In this loop, the counter variable is incremented by the step value (which defaults to 1 if not specified) for each iteration until it reaches the end value. The loop then ends, and the code execution continues with the next statement after the Next keyword.

On the other hand, the For Each loop is used to iterate through a collection or array by value. It has the following syntax:

For Each element In group
    [ statements to be executed inside the loop]
Next [element]

The element variable is assigned to each element in the group collection or array, one by one, until all elements have been processed. The loop then ends, and the code execution continues with the next statement after the Next keyword.

Here is an example of both loops:

overview image of Using For and For Each loop

Sub For_vs_For_Each_loop()
Dim arr(1 To 5) As Integer
Dim i As Integer
For i = 1 To 5
    arr(i) = i * 2
Next i
Dim item As Variant
For Each item In arr
    Debug.Print item
Next item
End Sub

Code Breakdown

Sub For_vs_For_Each_loop()

Defines a new subroutine named “For_vs_For_Each_loop”

Dim arr(1 To 5) As Integer

Declares an array variable “arr” of integer data type with 5 elements using “Dim” keyword

Dim i As Integer

Declares an integer variable “i” using “Dim” keyword

For i = 1 To 5

Starts a “For” loop with “i” variable initialized to 1 and ending at 5

 arr(i) = i * 2
Next i

In each iteration of the “For” loop, assign the value of “i” multiplied by 2 to the “arr” array element with the same index as “i”. Ends the “For” loop with “Next i” statement.

Dim item As Variant

Declares a “Variant” data type variable named “item” using “Dim” keyword

For Each item In arr

Starts a “For Each” loop with “item” variable assigned to each element in the “arr” array

Debug.Print item
Next item
End Sub

In each iteration of the “For Each” loop, prints the value of the “item” variable to the “Immediate Window” using the “Debug.Print” statement. Then, ends the “For Each” loop with “Next item” statement. Finally, ends the subroutine with “End Sub” statement.

Output of Using For and For Each Loop togethe

Some Examples of For Each Loop in Excel VBA

For Each loop are a fundamental concept in VBA programming that allows developers to loop through each element in an array, collection, or range. In this section, I have given 2 examples of using For Each loop which will give you a better understanding of For Each loop.

Example 1 – Apply a For Each Loop to Enter the Same Values in a Column

We used For Each loop to write the same value in 5 cells of a column.

Sub For_each_loop_example()
    Dim cell As Range
    For Each cell In Range("B4:B8")
        cell.Value = "Hello World!"
    Next cell
End Sub

Code Breakdown

Sub For_each_loop_example()

The Sub statement indicates the beginning of a subroutine, which is a block of code that performs a specific task. The name of the subroutine is “For_each_loop_example”.

 Dim cell As Range

The “Dim” statement declares a variable named “cell” as a Range data type. This variable will be used to refer to each cell in the specified range.

For Each cell In Range("B4:B8")
  cell.Value = "Hello World!"
    Next cell

The “For Each” loop is used to iterate through each cell in the range “B4:B8”. “Range(“B4:B8″)” specifies the range of cells that the loop will iterate through. “cell” is the loop variable that refers to each cell in the range as the loop iterates. The statement “cell.Value = “Hello World!”” sets the value of the current cell to the text string “Hello World!”. The “Next” statement indicates the end of the loop and moves the loop to the next cell in the range.

End Sub

End Sub indicates the end of the subroutine.

final output of VBA code to write same value in cells


Example 2 – Use the For Loop to Count Sheets of a Workbook

By using For Each loop, you can count the total number of datasheets of a workbook. This code can be used widely if there are so many sheets in a workbook.

Overview image of using For Each loop to count datasheets

Sub For_each_loop_CountSheets()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim ws As Worksheet
    Dim count As Integer
    For Each ws In wb.Worksheets
        count = count + 1
    Next ws
    Debug.Print "The workbook has " & count & " worksheets."
End Sub

Code Breakdown

Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim ws As Worksheet
    Dim count As Integer

The Dim statement is used to declare three variables. wb, which is declared as a Workbook object. ws, which is declared as a Worksheet object. count, which is declared as an Integer. The Set statement is used to assign the value of ActiveWorkbook to the variable wb.

For Each ws In wb.Worksheets
        count = count + 1
    Next ws
    Debug.Print "The workbook has " & count & " worksheets."

We use the For Each loop to iterate through each worksheet in the workbook, with ws will be assigned to each worksheet in turn. Within the loop, the value of the count will increment by 1. Once the loop has finished iterating through all worksheets, the Debug.Print statement will print a message to the Immediate window indicating how many worksheets were counted. The message includes the value of the count variable, which will represent the total number of worksheets in the workbook.

Read More: Excel VBA to Use For Loop with Two Variables


Download the Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Mahfuza Anika Era
Mahfuza Anika Era

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo