Excel VBA: Combined If and Or (3 Examples)

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


Download Practice Workbook

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


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


3 Suitable Examples for Combined If and Or in Excel VBA 

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


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.


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, receives 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. Click here to know them in detail.


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. And don’t forget to visit our site ExcelDemy for more posts and updates.

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

ExcelDemy
Logo