How to Continue Excel VBA For Loop (with Examples)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 

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.

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.

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.