Excel VBA: Combining If with And for Multiple Conditions

In this tutorial, we will demonstrate how to combine If with And for multiple conditions with VBA.

We use the If and Else statement to evaluate whether a criterion is True or False. If a statement is True, the VBA code executes particular code. And if the statement is False, the VBA code executes a different set of code.

Generic VBA Code of the If-Else Statement:

Consider the following code snippet:

Sub If_Else()

Dim val As Integer
val = 5

End Sub

We have a variable with a value of 5.

• To check whether the value is greater than 4 or not, use the 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, the following output is returned:

Now, what if we add another condition to find whether the value is also less than 10 or not?

• Add a new If statement to the VBA previous code as follows:

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, the following output is returned:

Alternatively, we can combine If with And to evaluate for multiple conditions in just a single line.

Generic VBA Code of an If-And Statement:

This is a much simpler method to add multiple conditions than nesting multiple If statements.

• Modify the previous code 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, the following output is returned:

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

Combining If with And for Multiple Conditions: 2 Suitable Examples

Method 1 – Combining If with And for Two Conditions

Consider the following dataset:

We have a dataset of some customers’ purchases. Our goal is to give them a discount based on the Amount and Status. There are two conditions here:

Steps:

• Press Alt+F11 on your keyboard to open the VBA editor.
• Select Insert > Module.

• Enter the following code in the module window that opens:

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

• Save the file.
• Press Alt+F8 on your keyboard to open the Macro dialog box.
• Select discount_amount.
• Click on Run.

We successfully used multiple conditions with the If statement and And logic in Excel VBA.

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

Method 2 – Using More Than Two Conditions

Now let’s combine three conditions in a single If-And statement in VBA. The procedure is the same. Consider the following dataset:

Our goal is to give the salespersons salary increments based on the following conditions:

Steps

• Press Alt+F11 on your keyboard to open the VBA editor.
• Select Insert > Module.

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

• Save the file.
• Press Alt+F8 on your keyboard to open the Macro dialog box.
• Select increment.
• Click on Run.

Only three employees met the conditions to get a 10% increment, with the rest receiving a 5.5% increment.

Things to Remember

✎ Adding And statements is much more efficient than using multiple nested If statements.

✎ All the conditions must be True in the If-And statement in order for the “True Code” to be executed.

Download Practice Workbook

Related Articles

• 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 Sheet Exists