Excel VBA: Combining If with And for Multiple Conditions

In Microsoft Excel, we use VBA codes for a lot of applications. VBA codes help us to perform many operations with ease. One of them is using multiple conditions. We can utilize our VBA codes to combine multiple conditions that can give us our desired result in a short period. And we will do that by combining the If with And statement. In this tutorial, you will learn to combine If with And for multiple conditions with Excel VBA.

This tutorial will be on point with suitable examples and proper illustrations. So, read the whole article to develop your Excel knowledge.

How to Use If with And in VBA Codes

If you know VBA codes, then you know how to write an If statement to evaluate a condition. Basically, we try to assess criteria with True or False using the If and Else statement. If a statement is True, the VBA code executes a particular code. And if the statement is False, the VBA code performs other lines of codes.

Generic VBA Code of If-Else Statement:

Let’s take an example. Look at the following code snippet:

Sub If_Else()

Dim val As Integer
val = 5

End Sub

Here, we have a variable. And its value is 5. Now, we will check whether the value is greater than 4 or not. To do this, try the simple following code:

Sub If_Else()

Dim val As Integer
val = 5

If val > 4 Then
    MsgBox "The Value is Greater Than 4"
Else
    MsgBox "The Value is Less Than 5"
End If

End Sub

After running the code, you will get the following output:

Now, what if I add another condition to find whether the value is also less than 10 or not? To do that, we have to add a new If statement in the VBA code.

Sub If_Else()

Dim val As Integer
val = 5

If val > 4 Then
    If val < 10 Then
        MsgBox "The Value is Greater Than 4" & vbCrLf & _
                "And Also Less Than 10"
    End If
Else
    MsgBox "The Value is Less Than 5"
End If

End Sub

After running the code, you will get the following output:

Here, comes the And statement. We can combine If with And for multiple conditions in a single line.

Generic VBA Code of  If-And Statement:

It is hassle-free. Because it saves you a lot of time as you don’t have to insert multiple If statements in your VBA code. You can simply add multiple conditions using the If with And statement in Excel.

For the previous code, we can modify it to the following:

Sub If_Else_And()

Dim val As Integer
val = 5

If val > 4 And val < 10 Then
        MsgBox "The Value is Greater Than 4" & vbCrLf & _
                "And Also Less Than 10"
Else
    MsgBox "The Value is Less Than 5"
End If

End Sub

After running the code, you will get the following output:

So, you can see, the code is working just fine to add multiple conditions using If with And in Excel VBA.

Read More: Excel VBA: If Statement Based on Cell Value

Excel VBA to Combine If with And for Multiple Conditions: 2 Suitable Examples

In the following sections, we are going to provide you with 2 examples with Excel VBA to combine If with And for multiple conditions. We recommend you to learn and apply these methods to your Excel worksheet. It will surely develop your Excel knowledge.

1. Combining If with And for Two Conditions

In this example, you will learn to combine If with And for multiple conditions using the VBA code. Take a look at the dataset:

Here, we have a dataset of some customers’ purchases. Now, our goal is to give them a discount based on the Amount and Status. There will be two conditions here:

Let’s follow these simple steps:

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• After that, type the following code:

Sub discount_amount()

Dim cell, output_rng As Range
Set output_rng = Range("E5:E9")

For Each cell In output_rng
    If cell.Offset(0, -2) > 450 And cell.Offset(0, -1) = "VIP"  Then
        cell.Value = "10% Discount"
    Else
        cell.Value = "No Discount"
    End If
Next

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select dicount_amount.
• Then, click on Run.

As you can see, we successfully used multiple conditions with the If statement and And logic in Excel VBA. Give this a try.

Read More: Excel VBA: If Cell Contains Value Then Return a Specified Output

2. Utilizing More Than Two Conditions in Excel VBA

In this example, we will combine three conditions in a single If-And statement in VBA. The procedure is the same. You can add multiple conditions in a single statement by adding the And statement. Take a look at the following dataset:

Here, we have a dataset of some employees. Now, our goal is to give them increments based on some conditions.

Let’s follow these simple steps.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert > Module.

• After that, type the following code:

Sub increment()

Dim cell, output_rng As Range
Set output_rng = Range("F5:F12")

For Each cell In output_rng
    If cell.Offset(0, -3) < #1/1/2020# _
    And cell.Offset(0, -2) > 30 _
    And cell.Offset(0, -1) < 5000 _
    Then
        cell.Value = "10%"
    Else
        cell.Value = "5.5%"
    End If
Next

End Sub

• Then, save the file.
• After that, press Alt+F8 on your keyboard to open the Macro dialog box.
• Next, select increment.
• Then, click on Run.

As you can see, only three employees met the conditions to get a 10% increment and the rest of them are getting a 5.5% increment. So, here we also successfully used multiple conditions combining If with And using Excel VBA. In this way, you can add more conditions to an If statement in VBA.

💬 Things to Remember

✎ Adding And statement is much better rather than using the multiple nested If statements.

✎ Remember, all the conditions must need to be True in the If-And statement to execute the “True Code”.

Download Practice Workbook

Download this practice workbook that we’ve used to prepare this article.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to combine If with And for multiple conditions using Excel VBA. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Keep learning new methods and keep growing!

Related Articles

• Excel VBA: Combined If and Or
• Excel VBA Nested If Then Else in a For Next Loop
• Excel VBA to Check If String Contains Letters
• Else Without If Error VBA in Excel
• Excel VBA: Check If a File Exists or Not
• Excel VBA: Check If a Sheet Exists