VBA IF AND (Test Multiple Conditions)

Contents hide

AND in VBA

In VBA, the AND operator checks if multiple conditions are true simultaneously. For example, If condition1 And condition2 Then. Both conditions must be true for the combined condition to be true. If any of the conditions are false, the combined condition is false.

Combining IF & AND

You can see in the below snapshot that you can use AND within the IF statement and specify to conditions to text. But now let’s write a code with this:

First, start the IF statement with the “IF” keyword.
After that, specify the first condition that you want to test.
Next, use the AND keyword to specify the second condition
In the end, specify the second condition that you want to test.

To have a better understanding let’s see an example.

Sub myMacro()
If 1 = 1 And 2 > 1 Then
    MsgBox "Both of the conditions are true."
Else
    MsgBox "Maybe one or both of the conditions are true."
End If
End Sub

If you look at the above example, we have specified two conditions one if (1 = 1) and the second is (2 > 1) and here both of the conditions are true, and that’s why it executes the line of code that we have specified if the result is true.

Now lets if one of these two conditions is false, let me use a different code here.

Sub myMacro1()
If 1 = 1 And 2 < 1 Then
    MsgBox "Both of the conditions are true."
Else
    MsgBox "Maybe one or both of the conditions are true."
End If
End Sub

This VBA code checks two conditions:

1 = 1 (which is true)
2 < 1 (which is false)

When you run this macro, since both conditions need to be true for the IF statement to be returned TRUE, here one is false; it runs the Else part code and shows the message “Maybe one or both of the conditions are true.”

Multiple Conditions with IF & AND

In the same way, you can also test more than two conditions at the same time. Let’s continue the above example and add the third condition to it.

Sub myMacro2()
If 1 = 1 And 2 > 1 And 1 - 1 = 0 Then
    MsgBox "All the conditions are true."
Else
    MsgBox "Some conditions are false."
End If
End Sub

Now we have three conditions to test and we have used the AND after the second condition to specify the third condition. As you learned above that when you use AND, all the conditions need to be true to get true in the result.

When you run this code, it executes the line of code that we have specified for the true.

And if any of the conditions is false, just like you have in the following code, it returns false.

Sub myMacro3()
If 1 = 1 And 2 < 1 And 1 + 1 = 0 Then
    MsgBox "All the conditions are true."
Else
    MsgBox "Some conditions are false."
End If
End Sub

More Examples

Here are a few examples that show you how to write code in VBA using an IF statement and AND in combination.

This VBA code below checks if num1 is greater than 5 and num2 is less than 30. If both conditions are true, it shows, “Both conditions are true.” Otherwise, it shows “One or both conditions are false.”

Sub myMacro4()
    Dim num1 As Integer
    Dim num2 As Integer
    
    num1 = 10
    num2 = 20
    
    If num1 > 5 And num2 < 30 Then
        MsgBox "Both conditions are true."
    Else
        MsgBox "One or both conditions are false."
    End If
End Sub

The VBA code below checks if the value in cell A1 is greater than 50 and in cell B1 is less than 100. If both conditions are true, it says, “Cell A1 is greater than 50 and Cell B1 is less than 100.” Otherwise, it shows “One or both conditions are false.”

Sub myMacro5()
    Dim cellA As Range
    Dim cellB As Range
    
    Set cellA = Range("A1")
    Set cellB = Range("B1")
    
    If cellA.Value > 50 And cellB.Value < 100 Then
        MsgBox "Cell A1 is greater than 50 and Cell B1 is less than 100."
    Else
        MsgBox "One or both conditions are false."
    End If
End Sub

The VBA code below checks if the text is “Hello” and the score is greater than 80. If both conditions are true, it shows a message box saying, “Text is ‘Hello’ and score is greater than 80.” Otherwise, it shows “One or both conditions are false.”

Sub myMacro6()
    Dim text As String
    Dim score As Integer
    
    text = "Hello"
    score = 85
    
    If text = "Hello" And score > 80 Then
        MsgBox "Text is 'Hello' and score is greater than 80."
    Else
        MsgBox "One or both conditions are false."
    End If
End Sub

The code below checks if the age is above 18. If true, it then checks if the score is between 80 and 100. Depending on these conditions, it shows appropriate messages about the age and score. If the age is 18 or below, it shows a message for that case.

Sub myMacro7()
    Dim age As Integer
    Dim score As Integer
    
    age = 22
    score = 90
    
    If age > 18 Then
        If score > 80 And score < 100 Then
            MsgBox "Age is above 18 and score is between 80 and 100."
        Else
            MsgBox "Age is above 18 but score is not in the desired range."
        End If
    Else
        MsgBox "Age is 18 or below."
    End If
End Sub

What is VBA

AND in VBA

Combining IF & AND

Multiple Conditions with IF & AND

More Examples

Related Tutorials