VBA If Statement with And Operator [4 Examples]
VBA If..And statement is used when you have to test multiple conditions to execute one or more statements.
Purpose of VBA if and statement
VBA If..And statement is used when you have to test multiple conditions to execute one or more statements.
A simple example of And with If with two conditions
We have two integer type variables. In the If statement, we will test their values and display an alert:
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Public Sub If_test() Dim i As Integer, j As Integer i = 10 j = 20 If i = 10 And j = 20 Then MsgBox "Both Conditions are True!" Else MsgBox "One or Both False!" End If End Sub |
Result:
As both conditions are True, so the statement in the if statement is executed and it displayed the alert.
Syntax of using If..And
This is how you may use the And operator in the If statement in VBA:
|
1 2 3 4 5 |
If Condition1 And Condition2 Then ‘Statements to execute End If |
- If all the conditions are True then the code inside “If” will execute
- If any of the conditions is False – the simple If terminates or If ElseIf statement is given, its conditions will be tested
- For executing one or more statements if any of the conditions in the If statement is True, use the Or operator.
In the section below we will show you how to use it with Else If and Else statements in examples.
Using String in And operator example
In this example, we are testing two conditions with the variables of String type.
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub If_test() Dim Str1 As String, Str2 As String Str1 = "VBA" Str2 = "Excel" If Str1 = "VBA" And Str2 = "Java" Then MsgBox "Both Conditions are True!" Else MsgBox "One or Both Conditions are False!" End If End Sub |
Output:
You can see, we checked if str2=”Java” and as it is false, so one condition failed. As a result, Msgbox of the Else statement displayed.
Using Excel Cell data in the If..And
In this example, we will check two Excel cell data in the And operator. For that, we have the following sample sheet:
We will check a Product Name and Status and display its price in a message box if both conditions are True, otherwise Else message box displays:
Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Private Sub CommandButton1_Click() Dim Product_name As String, Product_Status As String Product_name = Range("B5").Value Product_Status = Range("D5").Value If Product_name = "Maze" And Product_Status = "In Stock" Then MsgBox "The Price = " & Range("C5").Value Else MsgBox "Product Does Not Exist!" End If End Sub |
Result:
Using three conditions with And operator
You may also use more than two conditions to check by And operator. Just like two conditions, all given conditions must be True in order to execute one or more lines of code inside that If..And block.
To demonstrate that, we are again using our above-mentioned sample sheet.
We give three conditions and if all are True, our given range of cells border will be applied to the sample sheet.
Have a look at the code and output:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Dim Product_id As String, Product_name As String, Product_Status As String Product_id = Range("A8").Value Product_name = Range("B8").Value Product_Status = Range("D8").Value If Product_id = "p-007" And Product_name = "Rice" And Product_Status = "Out of Stock" Then Range("A8:D8").BorderAround LineStyle:=xlContinuous, Weight:=xlThick Else MsgBox "Product Does Not Exist!" End If |
Output:
Note: You may place that code in a Macro, button click event etc. We executed this in the Button click event for our demo.
