VBA If Statement with And Operator [4 Examples]

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:

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, the statement in the if statement is executed and it displays the alert.

Syntax of using If..And

This is how you may use the And operator in the If statement in VBA:

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

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 that we checked if str2=”Java” and as it is false, so one condition failed.

As a result, Msgbox of the Else statement is 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:

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

Have a look at the code and output:

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: