Excel VBA Nested If Then Else in a For Next Loop (3 Examples)

In this article, we will explore three simple examples that demonstrate the practical application of nested If Then Else statements within a For Next loop. Although there are countless use cases for nested If statements, we have chosen these specific examples to provide a clear understanding of how they can be utilized within a For loop.

Example 1 – Summing Data Based on Multiple Criteria Using Nested If Within a For Loop

For the first example, we have a dataset containing details of product transactions for some customers of a shop.

We will set two criteria at the bottom (Customer and Product) and determine the sum of all the transactions that meet these criteria using a VBA code with Nested If Then Else in a For Next loop.

Here is the code:

VBA Code Syntax:

Sub Summing_Data()
Target_Customer = Range("D16").Value
Target_Product = Range("D17").Value
Total_Sum = 0
For i = 1 To 10
If Range("B5").Cells(i, 2) = Target_Customer Then
    If Range("B5").Cells(i, 3) = Target_Product Then
        Total_Sum = Total_Sum + Range("B5").Cells(i, 4)
    End If
End If
Next i
Range("D18").Value = Total_Sum
End Sub

How Does the Code Work?

Target_Customer = Range("D16").Value
Target_Product = Range("D17").Value

First, we assign the two criteria Customer and Product to the variables in cells D16 and D17 (Target_Customer and Target_Product respectively).

Total_Sum = 0

Then we take another variable named Total_Sum and assign the value of 0 to it.

For i = 1 To 10

If Range("B5").Cells(i, 2) = Target_Customer Then
    If Range("B5").Cells(i, 3) = Target_Product Then
        Total_Sum = Total_Sum + Range("B5").Cells(i, 4)
    End If
End If
Next i

We use a for loop to iterate through all 10 rows in our dataset. Then we use two IF statements, one for determining whether the corresponding CustomerName in the row is the same person as the Target_Customer, and the other for determining whether the corresponding Product cell is the same as Target_Product. If both criteria are satisfied, we add the value in the Price cell to the Total_Sum. This loop continues until the end of the dataset.

```
Range("D18").Value = Total_Sum
End Sub
```

After completing the For loop, we record the final Total_Sum in cell D18.

To test the code, we first insert a customer and product name in cells D16 and D17 respectively.

Then we run the code. The result is 83.86.

If we change the criteria in cells D16 and D17, we will get the results accordingly.

Example 2 – Conditional Formatting Using Nested If Within a For Loop

Now we will conditionally format cells using nested If Then Else in a For Next loop in VBA. In our sample dataset there are a set of numbers. We’ll highlight the cells that don’t contain positive numbers.

Here is the code utilizing nested If Then Else statements in a For Next loop.

VBA Code Syntax:

Sub Hlight_Ng_cells()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("B5:B14")
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If cell.Value > 0 Then
                'Do Nothing
            Else
               cell.Interior.Color = vbYellow
            End If
        Else
            cell.Interior.Color = vbYellow
        End If
    Next cell
    Exit Sub
End Sub

How Does the Code Work?

 Set rng = Range("B5:B14")

The range of the dataset B5:B14 is assigned to the Range type variable rng.

For Each cell In rng

A For Each loop iterates through each cell in the rng.

If IsNumeric(cell.Value) Then
            If cell.Value > 0 Then
                'Do Nothing

Inside the For Each loop, there are two If statements (nested Ifs). In the first If statement, we check whether the current cell contains numeric data. If the answer is True then we apply another If statement to check whether the numerical value of the cell is positive. If this is also True then we do nothing.

Else
      cell.Interior.Color = vbYellow
              End If

If the cell contains a non-positive number, we change the background color of the cell to yellow.

Else
                        cell.Interior.Color = vbYellow
            End If

On the other hand, if the cell doesn’t contain any numbers, we also change the background color of the cell to yellow.

When we run the code, it highlights those cells that don’t contain positive numbers.

Example 3 – Sorting Data Using Nested If Inside a For loop

Here, we have a dataset that contains employees’ data (ID, Name, Department and Salary).

We’ll use a VBA code to sort the data according to the Department, and then sort the same departmental data in ascending order of the salaries of the employees, using Nested IF Then Else statements in the For loop.

The VBA code is as follows:

VBA Code Syntax:

Sub SortingData()
sorted_row = 0
'---------Creating List of Department----------'
Dim Department(4) As Variant
Dim i As Integer
For i = 0 To 3
    Department(i) = Choose(i + 1, "HR", "IT", "Finance", "Marketing")
Next i
'----Looping Throug each Department---------'
For d = 0 To 3
department_IR = sorted_row + 1
'--Looping Through each row in the dataset--'
    For iRow = 1 To 10
        If Range("B5").Cells(iRow, 3) = Department(d) Then
            '-----Incrementing the no of sorted rows----'
            sorted_row = sorted_row + 1
            'Swaping the both rows
                For c = 1 To 4
                    dummy = Range("B5").Cells(sorted_row, c)
                    Range("B5").Cells(sorted_row, c) = Range("B5").Cells(iRow, c)
                    Range("B5").Cells(iRow, c) = dummy
                Next c
        End If
    Next iRow
department_FR = sorted_row
'---Sorting Ascending order with respect to Salary----'
    Count = 0
    For k = department_IR To department_FR - 1
        For j = department_IR To department_FR - Count - 1
            If Range("B5").Cells(j, 4) > Range("B5").Cells(j + 1, 4) Then
                ' Swapping rows
                For c = 1 To 4
                    temp = Range("B5").Cells(j, c)
                    Range("B5").Cells(j, c) = Range("B5").Cells(j + 1, c)
                    Range("B5").Cells(j + 1, c) = temp
                Next c
            End If
  Count = Count + 1
        Next j
    Next k
'-------Sorting Completed--------------'
Next d
End Sub

How Does the Code Work?

Sub SortingData()
sorted_row = 0
Dim Department(4) As Variant
Dim i As Integer
For i = 0 To 3
    Department(i) = Choose(i + 1, "HR", "IT", "Finance", "Marketing")
Next i

We define an array (Department) to store all the departments in it. Then we apply a For loop to assign a value to each element in the array.

For d = 0 To 3
department_IR = sorted_row + 1
    For iRow = 1 To 10
                Next iRow
Next d

We apply another For loop (For d…Next d) to loop through each department. Inside this For loop, we arrange each department’s data serially and with ascending salaries. Then, inside the first For loop, we use another For loop (For iRow….Next iRow) to loop through each row in the dataset.

If Range("B5").Cells(iRow, 3) = Department(d) Then
            sorted_row = sorted_row + 1
            'Swaping the both rows
                For c = 1 To 4
                    dummy = Range("B5").Cells(sorted_row, c)
                    Range("B5").Cells(sorted_row, c) = Range("B5").Cells(iRow, c)
                    Range("B5").Cells(iRow, c) = dummy
                Next c
        End If

Inside the iRow For loop, we first check if the iteration row is from the department row using an If statement. If the iterative is actually from the same department, then we swap both rows. This gathers all the rows from the same department together.

department_FR = sorted_row
    Count = 0
    For k = department_IR To department_FR - 1
        For j = department_IR To department_FR - Count - 1
            If Range("B5").Cells(j, 4) > Range("B5").Cells(j + 1, 4) Then
                ' Swapping rows
                For c = 1 To 4
                    temp = Range("B5").Cells(j, c)
                    Range("B5").Cells(j, c) = Range("B5").Cells(j + 1, c)
                    Range("B5").Cells(j + 1, c) = temp
                Next c
            End If
  Count = Count + 1
        Next j
    Next k
Next d
End Sub

Finally, after bringing all the data from the same departments together, we use the bubble sorting algorithm to sort the same departmental data in ascending order of their salary.

After running the code, we get the following result:

All the employees from the same department are grouped together in ascending order in terms of their salary.

Things to Remember

When using the Nested IF statement, you must write End IF at the end, otherwise you will get an error.
When using multiple For loops, it is critical to enter Next in the proper position. Otherwise, the code will behave incorrectly.

Download Practice Workbook

Nested IF in For Next.xlsm