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

If Condition Then

True Code

Else

False Code

End If

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:

If Condition 1 And Condition 2 Then

True Code

Else

False Code

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:

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

• If the amount is more than \$450 and the status is “VIP”, give them a 5% discount.
• Otherwise, there is no discount.

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

We use a For-Next loop to compare each cell’s value.

cell.Offset(0, -2) indicates the amount.

cell.Offset(0, -1) indicates the status.

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

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

• If someone joined before January-20, is aged over 30, and has a current salary of less than \$5000, give them a 10% increment.
• Otherwise, give them a 5% increment.

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

We use a ForNext loop to compare each cell’s value.

cell.Offset(0, -3) indicates the Joining Date.

cell.Offset(0, -2) indicates the Age.

cell.Offset(0, -1) indicates the Current Salary.

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

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF