Excel VBA: For Loop Skip to Next Iteration

In Excel VBA we often have to use the For Loop. This loop is particularly very helpful for long iterations. We can also use this loop to skip to the next iteration. This article will show some methods to how you can use the For Loop and skip to the next iteration with Excel VBA.

For Loop Overview

For Loop is used for carrying out a specified number of iterations within a given condition.

It starts with a For statement and ends with Next statement. Let’s see an example of For Loop.

Sub For_Loop()
For i = 1 To 20 Step 2
Debug.Print i
Next i
End Sub

This is a very simple For Loop. The code prints the value of i as 1,3,5,7 etc. The output is given below.

Excel VBA: For Loop Skip to Next Iterations: 2 Ways

Generally, Loops in Excel VBA are continuous instructions. But, for practical purposes, we may have to skip the loop and go to the next iteration. In this article, we will show you how you can skip to the next iterations in the for loop with the Step and GoTo statements in Excel VBA.

1. Skip to Next Iteration in For-Next Loop with Step Statement

To skip to the next iteration, we can use the For-Next loop with the Step statement.

The Step statement skips the iteration number in a specified number.

We have taken this dataset of different St. IDs and their marks in different subjects.

In this procedure, we will show you how we can highlight alternate rows using the simple For Loop with the Step statement.

First, you have to select a specific date range. We have selected B5:E16.

Insert this Code in a new Module and Click on the Run button.

Sub Skip_to_next_with_step()
For i = 2 To Selection.Rows.Count Step 2
Selection.Rows(i).Interior.ColorIndex = 35
Next i
End Sub

For i = 2 To Selection.Rows.Count Step 2

Selection.Rows(i).Interior.ColorIndex = 35
Next i

Running the code, your data table will look like this where all the alternative rows will be highlighted.

2. Skip Through GoTo Statement

The GoTo statement allows the program to jump to a specific line of the code indicated by a specific label. This statement can be used to exit the loop and also for error handling.

We are using the same dataset as previously shown. This time, we will use the GoTo statement to skip iteration. Let’s assume the pass mark is 40. In this procedure, we will highlight the mark cells on the basis of the pass mark.

Select the data range consisting of only marks. For our dataset, we have selected C5:E16 range of cells.

Open a new module in your workbook and Insert the following code.

Then, Click on the Run button.

Sub skip_Through_GoTo()
    For Each cell In Selection
        If cell.Value >= 40 Then
            cell.Interior.Color = RGB(191, 249, 193)
        Else
        GoTo Failed_Criteria
        End If
Failed_Criteria:
     If cell.Value < 40 Then
            cell.Interior.Color = RGB(255, 168, 168)
     End If
        Next cell         
End Sub

For Each cell In Selection
        If cell.Value >= 40 Then
            cell.Interior.Color = RGB(191, 249, 193)

 Else
        GoTo Failed_Criteria
        End If

Failed_Criteria:

 If cell.Value < 40 Then
            cell.Interior.Color = RGB(255, 168, 168)
     End If
        Next cell

In the end, your data table will look like the below image where the cells with pass marks will be highlighted in green and the cells with fail marks will be highlighted in red.

How to Continue a Do-While Loop in Excel VBA

In VBA Do Loop allows a certain portion of code to run continuously until the given condition is valid. There are two types of Do Loops: Do While and Do Until. Let’s see how we can use these loops.

The Do While Loop continues to run until a certain condition is true. The Program will stop when the condition is false.

We have taken this dataset of St. IDs and their marks for different subjects.

Now, with the Do-While Loop, we are going to find out the cells with failed and passed Criteria.

So, Insert this VBA code in a new Module and click the Run button.

Sub skip_Through_GoTo_Do_while()
Dim rw, cl As Integer
rw = 5
Do While rw < 17
cl = 3
    Do While cl < 6
        If Cells(rw, cl).Value >= 40 Then
            Cells(rw, cl).Interior.Color = RGB(191, 249, 193)
        Else
            GoTo Failed_Criteria
        End If
Failed_Criteria:
 If Cells(rw, cl).Value < 40 Then
            Cells(rw, cl).Interior.Color = RGB(255, 168, 168)
     End If
     cl = cl + 1
     Loop
rw = rw + 1
Loop
End Sub

Sub skip_Through_GoTo_Do_while()
Dim rw, cl As Integer
rw = 5
Do While rw < 17
cl = 3
    Do While cl < 6

If Cells(rw, cl).Value >= 40 Then
        Cells(rw, cl).Interior.Color = RGB(191, 249, 193)

  Else
            GoTo Failed_Criteria
   End If
If the previous condition does not satisfy, then the code will skip the loop and will move on to the Failed_Criteria label.

Failed_Criteria:

 If Cells(rw, cl).Value < 40 Then
            Cells(rw, cl).Interior.Color = RGB(255, 168, 168)
     End If
     cl = cl + 1
     Loop
rw = rw + 1
Loop

Read More: How to Continue Excel VBA For Loop

How to Continue a Do-Until Loop in Excel VBA

The Do Until Loop is quite similar to the Do While Loop only the condition is opposite. With Do Until Loop, the code will continue to run as long as the condition is false and it will stop when the condition becomes true. We are going to use the same dataset and almost the same code for this method.

For this procedure, we will use the same dataset as shown before.

Copy the below code in a new module.

After that Run the Module.

Sub skip_Through_GoTo_Do_until()
Dim rw, cl As Integer
rw = 5
Do Until rw = 17
cl = 3
    Do Until cl = 6
        If Cells(rw, cl).Value >= 40 Then
            Cells(rw, cl).Interior.Color = RGB(191, 249, 193)
        Else
            GoTo Failed_Criteria
        End If
Failed_Criteria:
     If Cells(rw, cl).Value < 40 Then
            Cells(rw, cl).Interior.Color = RGB(255, 168, 168)
     End If
     cl = cl + 1
    Loop
rw = rw + 1
Loop
End Sub

Sub skip_Through_GoTo_Do_until()
Dim rw, cl As Integer
rw = 5
Do Until rw = 17
cl = 3
    Do Until cl = 6

 If Cells(rw, cl).Value >= 40 Then
        Cells(rw, cl).Interior.Color = RGB(191, 249, 193)

  Else
            GoTo Failed_Criteria
       End If

Failed_Criteria:

   If Cells(rw, cl).Value < 40 Then
            Cells(rw, cl).Interior.Color = RGB(255, 168, 168)
     End If
     cl = cl + 1
     Loop
rw = rw + 1
Loop

Now, Copy the following code in a new module and Click on the Run button. And, we can see all the cells are highlighted accordingly.

How to Use Excel VBA Loops to Iterate Through Arrays and Ranges

There are different loops for Excel VBA. In these loops, we have to set data and we can do it in two ways. One is using Range and the other one is using Arrays.

1. VBA Loop to Iterate Through Ranges

In this procedure, we will add Remark on the basis of Total marks. So, we added a new column named Remarks.

First, we have to select the data range. For this code, we selected F5:F16 range of cells.

Now, Copy the following code in a new Module and then Click on the Run button.

Sub Loop_Rng()
For i = 1 To Selection.Rows.Count
If Range("F" & i + 4).Value > 200 Then
   Range("G" & i + 4).Value = "Good"
   Range("G" & i + 4).Interior.Color = RGB(20, 200, 120)
ElseIf Range("F" & i + 4).Value >= 150 _
And Range("F" & i + 4).Value < 200 Then
   Range("G" & i + 4).Value = "Average"
   Range("G" & i + 4).Interior.Color = RGB(20, 150, 150)
Else
  Range("G" & i + 4).Value = "Poor"
  Range("G" & i + 4).Interior.Color = RGB(170, 100, 100)
  End If
Next i
End Sub

Dim i As Integer
For i = 1 To Selection.Rows.Count

If Range("F" & i + 4).Value > 200 Then
   Range("G" & i + 4).Value = "Good"
   Range("G" & i + 4).Interior.Color = RGB(20, 170, 120)

ElseIf Range("F" & i + 4).Value >= 150 _
And Range("F" & i + 4).Value < 200 Then
   Range("G" & i + 4).Value = "Average"
   Range("G" & i + 4).Interior.Color = RGB(20, 160, 170)

Else
  Range("G" & i + 4).Value = "Poor"
  Range("G" & i + 4).Interior.Color = RGB(170, 100, 100)
  End If
Next i

After running the code, we get the Remarks Column filled with proper Remarks.

2. VBA Loop to Iterate Through Arrays

We have the same dataset as the previous procedures. The dataset has the Total marks of each student. Now, with the help of the VBA Array, we will add Remarks based on different criteria.

This is the code we will use for the procedure.

Copy this code in a new module and Click on the Run button.

Sub array_summation()
    Dim marksArray(1 To 12) As Variant
    Dim i As Integer
    For i = 1 To 12
    marksArray(i) = Range("F" & i + 4).Value
    Next i
    For i = 1 To 12
    If marksArray(i) >= 200 Then
    Range("G" & i + 4).Value = "Good"
    Range("G" & i + 4).Interior.Color = RGB(20, 200, 120)
    ElseIf marksArray(i) >= 150 And _
            marksArray(i) < 200 Then
       Range("G" & i + 4).Value = "Average"
       Range("G" & i + 4).Interior.Color = RGB(20, 150, 150)
    Else
    Range("G" & i + 4).Value = "Poor"
    Range("G" & i + 4).Interior.Color = RGB(170, 100, 100)
    End If
    Next i
End Sub

Dim marksArray(1 To 12) As Variant
    Dim i As Integer
    For i = 1 To 12
    marksArray(i) = Range("F" & i + 4).Value
    Next i

 For i = 1 To 12
    If marksArray(i) >= 200 Then
    Range("G" & i + 4).Value = "Good"
    Range("G" & i + 4).Interior.Color = RGB(20, 200, 120)

ElseIf marksArray(i) >= 150 And _
            marksArray(i) < 200 Then
       Range("G" & i + 4).Value = "Average"
       Range("G" & i + 4).Interior.Color = RGB(20, 150, 150)

Else
    Range("G" & i + 4).Value = "Poor"
    Range("G" & i + 4).Interior.Color = RGB(170, 100, 100)
    End If
    Next i

In the end, you will get the Remarks in the following image.

Read More: Excel VBA For Loop with Array

Frequently Asked Questions

1. How do you skip to the next iteration of a For Loop in VBA?

You can skip to the next iteration of a For Loop in VBA by Step and GoTo statement. Step statement is generally used where we need to iterate for uniform intervals. On the other hand, the GoTo statement is used when we need to skip the loop and move on to the next instruction.

2. How do you skip to the next while loop?

To skip Next While Loop, you need to use the Continue While statement. Also, you can use the GoTo statement to skip to the next while loop.

3. How do you do for next loop in VBA?

The For Next statement is the most frequently used loop in VBA. The For Next Loop is very easy to execute. It starts with a For statement with a specific condition and ends with a Next statement.

4. How do you jump to the end of a loop in Excel VBA?

To jump to the end of the For loop, you can use the Exit For statement. For any other loop, you can use the GoTo statement.

Download Practice Workbook

You can download and practice this workbook.

Conclusion

So, we have shown some useful ways to skip to the next iteration in For loop with Excel VBA. Also, we have shown how you can skip to the next iteration with the Do While and Do Until loops. We hope you find the content of this article useful. If there are further queries, feel free to mention them in the comment section.

Related Articles

• Excel VBA to Use For Loop with Two Variables
• How to Use Excel VBA Nested For Loop
• Excel VBA with For Loop Decrement
• Excel VBA: Running a Backwards For Loop
• Excel VBA: How to Exit a For Loop