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

Get FREE Advanced Excel Exercises with Solutions!

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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I completed my Bachelor's degree in Naval Architecture from BUET. Currently, I am working as an Excel and VBA Content Developer. Here, I always try to present solutions to Excel-related problems in a very concise and easy-to-understand manner. In my leisure time, I love to read books, listen to podcasts, and explore new things.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF