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:
If Condition Then
True Code
Else
False Code
End If
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:
If Condition 1 And Condition 2 Then
True Code
Else
False Code
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.
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:
- If the amount is more than $450 and the status is “VIP”, give them a 5% discount.
- Otherwise, there will be no discount.
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
We used a For-Next loop to compare each cell’s value.
Here, cell.Offset(0, -2) indicates the amount.
cell.Offset(0, -1) indicates the status.
- 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 combining If with And in Excel VBA. Give this a try.
Similar Readings
- Example of VLOOKUP with Multiple IF Condition in Excel
- VBA IF Statement with Multiple Conditions in Excel
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.
- If someone joined before January-20, is age more than 30, and current salary of less than $5000, give them a 10%
- Otherwise, give them a 5% increment.
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
We used a For–Next loop to compare each cell’s value.
Here, cell.Offset(0, -3) indicates the Joining Date.
cell.Offset(0, -2) indicates the Age.
cell.Offset(0, -1) indicates the Current Salary.
- 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.
Read More: Excel IF Function with 3 Conditions
💬 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!