Excel VBA: Combined If and Or (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

While working with VBA in Excel, we often have to use combined If and Or to carry on a task when at least one among multiple conditions is satisfied. In this article, Iâ€™ll show you how you can use combined If and Or in Excel VBA.

If with Or Statement in Excel VBA: Syntax

To use combined If and Or in Excel VBA, first, you have to separate two or more conditions (booleans) with an Or operator, along with a then statement.

Next, you have to write down the task thatâ€™ll be executed if at least one among the conditions is satisfied.

Finally, you have to close the If-block with an End If statement.

Therefore, the general syntax for using combined If and Or in VBA is:

``````If Condition 1 Or Condition 2 â€¦ Or â€¦ Condition n Then
End If``````

Combined If and Or in Excel VBA: 3 Suitable Examples

Here weâ€™ve got a data set that contains the names, joining dates, salaries, and genders of some employees of a company.

Now weâ€™ll use this data set to see a few examples of using a combination of If and Or in Excel VBA.

1. Combined If and Or Between Two Criteria from Two Different Columns

First of all, weâ€™ll use combined If and Or between two criteria from two different columns.

Letâ€™s try to find out the employees who joined after 2015 or receive a salary of less than \$50,000 in cell G4 of the worksheet.

The VBA code will be:

â§­ VBA Code:

``````Sub Combined_If_and_Or_1()

Set Rng = Range("B4:E13")

Set Destination = Range("G4")

Count = 1

For i = 1 To Rng.Rows.Count
Â Â Â  Joining_Date = Rng.Cells(i, 2)
Â Â Â  Salary = Rng.Cells(i, 3)
Â Â Â  If Joining_Date > CDate("31/12/2015") Or Salary < 50000 Then
Â Â Â Â Â Â Â  Destination.Cells(Count, 1) = Rng.Cells(i, 1)
Â Â Â Â Â Â Â  Count = Count + 1
Â Â Â  End If
Next i

End Sub``````

â§­ Output:

Run the code. Itâ€™ll extract the names of all the employees who joined after 2016 or get salaries less than \$50,000 in cell G4.

2. Combined If and Or Between Two Criteria from the Same Column

Now, weâ€™ll use combined If and Or between two criteria from the same column.

This time letâ€™s try to find out the employees who get a salary of less than \$40,000 or greater than \$80,000.

The VBA code will be:

â§­ VBA Code:

``````Sub Combined_If_and_Or_2()

Set Rng = Range("B4:E13")

Set Destination = Range("G4")

Count = 1

For i = 1 To Rng.Rows.Count
Â Â Â  Salary = Rng.Cells(i, 3)
Â Â Â  If Salary < 40000 Or Salary > 80000 Then
Â Â Â Â Â Â Â  Destination.Cells(Count, 1) = Rng.Cells(i, 1)
Â Â Â Â Â Â Â  Count = Count + 1
Â Â Â  End If
Next i

End Sub``````

â§­ Output:

Run the code. Itâ€™ll extract the names of all the employees who get salaries greater than 80,000 or less than 40,000 in cell G4.

3. Combined If and Or Between More Than Two Criteria

Finally, weâ€™ll use a combined If and Or between more than two criteria.

Weâ€™ll find out the employees who joined before 2016, receive a salary of more than 80,000 or is a male.

The VBA code will be:

â§­ VBA Code:

``````Sub Combined_If_and_Or_3()

Set Rng = Range("B4:E13")

Set Destination = Range("G4")

Count = 1

For i = 1 To Rng.Rows.Count
Â Â Â  Joining_Date = Rng.Cells(i, 2)
Â Â Â  Salary = Rng.Cells(i, 3)
Â Â Â  Gender = Rng.Cells(i, 4)
Â Â Â  If Joining_Date < CDate("1/1/2016") Or Salary > 80000 Or Gender = "Male" Then
Â Â Â Â Â Â Â  Destination.Cells(Count, 1) = Rng.Cells(i, 1)
Â Â Â Â Â Â Â  Count = Count + 1
Â Â Â  End If
Next i
End Sub``````

â§­ Output:

Run the code. Itâ€™ll extract the names of all the employees who joined before 2016 or get a salary of less than 40,000 or is a male in cell G4.

Things to Remember

Here Iâ€™ve focused on combining If and Or in VBA only. But if you want, you can combine If and And in VBA too.

Conclusion

So, these are the ways to use a combination of If and Or with VBA in Excel. Do you have any questions? Feel free to ask us.

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:

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelorâ€™s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF