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.


Combined If and Or in Excel VBA (Quick View)

VBA Code to Use Combined If and Or in 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
    Task
End If

VBA Code to Use Combined If and Or in VBA


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.

Data Set for Combined If and Or in Excel VBA

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.

Output for Combined If and Or in Excel VBA

Read More: Excel VBA: Combining If with And for Multiple Conditions


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

VBA Code for Combined If and Or in Excel VBA

â§­ 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.

Read More: VBA IF Statement with Multiple Conditions in Excel


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.


Download Practice Workbook

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


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.


Related Articles

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo