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

In this article, we explore the practical use of VBA nested If Then…Else statements within a For…Next loop in Excel. The For loop is commonly used for repeating tasks, while nested If Else statements allow for logical testing. By combining these two techniques, we gain better control over our code and can achieve impressive results. To learn more about how to use them effectively, read this article carefully.

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

In this section, 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. Let’s dive into each example and uncover their insights one by one.

1. Summing Data Based on Multiple Criteria Using Nested If Within For Loop

In the first example, we have a dataset where there are details of product transactions for some customers of a shop.

Here, we will set two criteria at the bottom (Customer and Product) and determine the sum of all the transactions that meet these criteria. For this, we can use 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 to 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

Here, we used a for loop to iterate through all 10 rows in our dataset. Next, we used 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 write the final Total_Sum in cell D18.

Now, to test the code, we first inserted a customer and product name in cells D16 and D17, respectively.

Then we ran the code. The result was 83.86.

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

2. Conditional Formatting Using Nested If Within For loop

In this second example, we will conditionally format cells using VBA nested If Then Else in a For Next loop. To illustrate, I have taken a dataset where there are a set of numbers. From here, we need to highlight the cells that don’t contain positive numbers.

Now, if we want to highlight the cells that don’t contain positive numbers, we can use the following VBA code that utilizes the nested If Then Else 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")
```

Here, the range of the dataset B5:B14 is set to the Range type variable rng.

```
For Each cell In rng
```

Here, a For Each loop will iterate 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 concerned 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. On the other hand, if this is also True then we do nothing.

Else
      cell.Interior.Color = vbYellow
              End If

If the cell indeed 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, then we change the background color of the cell to yellow.

Now, if we run the code, it will highlight those cells that don’t contain positive numbers.

3. Sorting Data Using Nested If Inside For loop

In this example, we will see another application of the VBA nested IF Then Else statement in a For Next loop. Here, we have a dataset that contains employees’ data (ID, Name, Department and Salary)

Here, we will use a VBA code that will sort the data according to the Department and then sort the same departmental data in ascending order of the salaries of the employees. To do that, we will use the Nested IF Then Else statement in the For loop. The VBA code that we will use 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

First of all, we take an array(Department) to store all the departments in it. Then apply 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

Then we apply another For loop (For d…Next d) to loop through each department. Inside this For loop, we will arrange each department’s data serially and with ascending salaries. Then, inside the first For loop, we take 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 will bring 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 department together, we use the bubble sorting algorithm to sort the same departmental data in ascending order of their salary.

After running the code, we will get the following result.

Here, we can see that all the employees from the same department are grouped together, and they are in ascending order in terms of their salary.

Things to Remember

While using the Nested IF statement, you must write End IF at the end, otherwise, you will get an error.
While using multiple For loops, it is very important to write Next in the proper position. Otherwise, the code will behave wrongfully.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Nested IF in For Next.xlsm

Conclusion

That is the end of this article regarding the use of VBA nested If Then…Else statements within a For…Next loop in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.

Get FREE Advanced Excel Exercises with Solutions!

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

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

1. Summing Data Based on Multiple Criteria Using Nested If Within For Loop

2. Conditional Formatting Using Nested If Within For loop

3. Sorting Data Using Nested If Inside For loop

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply