Excel VBA: If Then Else Statement with Multiple Conditions (5 Examples)

This article illustrates 5 different examples to set multiple conditions using the If…Then…Else statement in Excel VBA code.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

5 Examples of Using If… Then… Else Statement with Multiple Conditions in Excel VBA

In this article, we’ll work on grading an exam result based on a specific grading system. To do that, we’re going to set multiple conditions in the If…Then…Else statement in our VBA code.

Write Code in Visual Basic Editor

To set multiple conditions with the If…Then…Else statement, we need to open and write VBA code in the visual basic editor. Follow the steps to open the visual basic editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option.

• In the Visual Basic For Applications window, click the Insert dropdown to select the New Module option.

Now put your code inside the visual code editor and press F5 to run it.

1. Set Multiple Conditions Using If…Then… Else Statement in Excel VBA

1.1 Use of the Logical Operator – AND

Task: Find and print the grade (cell D3) for the subject of Physics (cell C3) based on the given grading system.

Solution: In this case, we’re going to configure the If…Then…Else statement in an ElseIf structure. Each of the Elseif conditions represents a single condition. With this structure whenever a condition is met as True, all the subsequent Elseif conditions are then skipped.

Here we also use the And logical operator to set the conditions. With this, we configured each of the ElseIf statements so that the number must fulfill both the conditions as True. Otherwise, it will skip to the next ElseIf statement.

Code: Let’s run the following code in the visual basic editor and press F5 to run it.

Sub IfThenElseMultipleConditions()
    If Range("C3").Value >= 80 Then
        Range("D3").Value = "A+"
    ElseIf Range("C3").Value < 80 And Range("C3").Value >= 70 Then
        Range("D3").Value = "A"
    ElseIf Range("C3").Value < 70 And Range("C3").Value >= 60 Then
        Range("D3").Value = "B"
    ElseIf Range("C3").Value < 60 And Range("C3").Value >= 50 Then
        Range("D3").Value = "C"
    ElseIf Range("C3").Value < 50 And Range("C3").Value >= 40 Then
        Range("D3").Value = "D"
    Else: Range("D3").Value = "Failed"
    End If
End Sub

Output: The result is A+ in cell D3 as the number of Physics (85) is greater than 80, it fulfills the first condition set in the If…Then…Else statement.

1.2 Use of the Logical Operator – OR

Task: We’ll check if there is a subject with marks less than 40. If the condition is true for any of the subjects, the student has failed the exam.

Solution: We need to use the Or logical operator to set the conditions in the If…Then…Else statement.

Code: Let’s run the following code in the visual basic editor and press F5 to run it.

Sub IfThenElseMultipleConditions()
If Range("C3") < 40 Or Range("C4") < 40 Or Range("C5") < 40 Or Range("C6") < 40 _
Or Range("C7") < 40 Or Range("C8") < 40 Or Range("C9") < 40 Then
MsgBox "Failed"
Else
MsgBox "Passed"
End If
End Sub

Output: As we have a subject with marks less than 40 i.e., 25, the MsgBox shows the message “Failed”.

Read More: VBA IF Statement with Multiple Conditions in Excel (8 Methods)

2. Set Multiple Conditions with Multiple If…Then…Else Statements

Task: Find and print the grade (cell D4) for the subject of Chemistry (cell C4) based on the given grading system.

Solution: in this example, we’ll use multiple If…Then…Else statements to set multiple conditions. Each of the statements will represent a single condition. This method is not efficient compared with the previous one. This method makes the code longer and runs all the statement blocks even if the condition is already found.

Code: Let’s run the following code in the visual basic editor and press F5 to run it.

Sub IfThenElseMultipleConditions()
    If Range("C4").Value >= 80 Then
        Range("D4").Value = "A+"
    End If
    If Range("C4").Value < 80 And Range("C4").Value >= 70 Then
        Range("D4").Value = "A"
    End If
    If Range("C4").Value < 70 And Range("C4").Value >= 60 Then
        Range("D4").Value = "B"
    End If
    If Range("C4").Value < 60 And Range("C4").Value >= 50 Then
        Range("D4").Value = "C"
    End If
    If Range("C4").Value < 50 And Range("C4").Value >= 40 Then
        Range("D4").Value = "D"
    End If
    If Range("C4").Value < 40 Then
        Range("D4").Value = "Failed"
    End If
End Sub

Output: The result is A in cell D4 as the number of Physics (85) is greater than 70 but less than 80, it fulfills the first condition set in the If…Then…Else statement.

Read More: Excel If Statement with Multiple Conditions in Range (3 Suitable Cases)

3. Use of Nested If…Then…Else to Set Multiple Conditions in Excel VBA

Task: We want to check whether the student gets A+ in (i) both Mathematics and History (ii) only in Mathematics (iii) only in History (iv) none of the subjects. In this example, the marks obtained in Mathematics and History are 91(cell C5) and 65 (cell C6) respectively.

Solution: To accomplish the task, we’ll use nested If…Then…Else statements and show the output in the MsgBox.

Code: Let’s run the following code in the visual basic editor and press F5 to run it.

Sub IfThenElseMultipleConditions()
    If Range("C5").Value >= 80 Then
        If Range("C6").Value >= 80 Then
        MsgBox "Got A+ in both Mathematics and History"
        Else: MsgBox "Got A+ only in Mathematics"
        End If
    Else
        If Range("C6").Value >= 80 Then
        MsgBox "Got A+ only in History"
        Else: MsgBox "Didn't get A+ in any of the subjects"
        End If
    End If
End Sub

Output: The MsgBox showed the following message.

Now, we change the marks of History to any number greater than 80, say 82, and run the code again. The output is shown in the following screenshot.

Read More: Excel If Function with Multiple Conditions (Nested IF)

Similar Readings

• Excel IF Function with 3 Conditions
• IF with AND in an Excel Formula (7 Examples)
• Excel VBA: Combining If with And for Multiple Conditions

4. Nest If…Then…Else Within For…Next loop to Set Multiple Conditions in Excel VBA

Task: Find and print grades for all the subjects in the list (C3:C9) in cells D3:D9.

Solution: Here we need to use the For…Next loop to run the If…Then…Else statement for each of the subject numbers in cells C3:C9.

Code: Let’s run the following code in the visual basic editor and press F5 to run it.

Sub IfThenElseMultipleConditions()
For i = 3 To 9
    If Range("C" & i).Value >= 80 Then
        Range("D" & i).Value = "A+"
    ElseIf Range("C" & i).Value < 80 And Range("C" & i).Value >= 70 Then
        Range("D" & i).Value = "A"
    ElseIf Range("C" & i).Value < 70 And Range("C" & i).Value >= 60 Then
        Range("D" & i).Value = "B"
    ElseIf Range("C" & i).Value < 60 And Range("C" & i).Value >= 50 Then
        Range("D" & i).Value = "C"
    ElseIf Range("C" & i).Value < 50 And Range("C" & i).Value >= 40 Then
        Range("D" & i).Value = "D"
    Else: Range("D" & i).Value = "Failed"
    End If
Next
End Sub

Output: The result is in cells D3:D9 based on the conditions set in the If…Then…Else statement.

Read More: How to Use Multiple IF Condition in Excel (3 Examples)

5. Use of If…Then…Else Statement to Set Multiple Conditions with Variables in Excel VBA

Task: We want to check the grading of a user input number.

Solution: We need to define a variable and set the value of that variable with a user input number. The variable will be checked for each of the conditions set by the If…Then…Else statement in the VBA code.

Code: Here, we defined variable named marks and use an input box to get the value from the user.  Let’s run the following code in the visual basic editor and press F5 to run it.

Sub IfThenElseMultipleConditions()
Dim marks As Long
marks = InputBox("Enter the Obtained Marks:")
    If marks >= 80 Then
        MsgBox "Obtained Grade: A+"
    ElseIf marks < 80 And marks >= 70 Then
          MsgBox "Obtained Grade: A"
    ElseIf marks < 70 And marks >= 60 Then
        MsgBox "Obtained Grade: B"
    ElseIf marks < 60 And marks >= 50 Then
        MsgBox "Obtained Grade: C"
    ElseIf marks < 50 And marks >= 40 Then
         MsgBox "Obtained Grade: D"
    Else:   MsgBox "Failed"
    End If
End Sub

Output: Running the code showed an input box to set the value of the predefined variable named marks. We put 74 to check the grading of it.

A MsgBox showed the output.

Read More: How to Use Multiple If Conditions in Excel for Aging (5 Methods)

Notes

We could also use the Select Case statement for similar kinds of tasks as we did with the If…Then…Else statement. The key difference is the Select Case statement evaluates an expression only once while each ElseIf allows us the evaluation of a different expression in the If Then Else statement with multiple conditions in excel VBA

Conclusion

Now, we know how to set multiple conditions in the If Then Else statement using VBA in Excel with the help of suitable examples. Hopefully, it would help you to use the functionality more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• Example of VLOOKUP with Multiple IF Condition in Excel (9 Criteria)
• Excel VBA: Combined If and Or (3 Examples)
• How to Use PERCENTILE with Multiple IF Condition in Excel (3 Examples)
• Excel IF between Multiple Ranges (4 Approaches)
• How to Use Multiple IF Statements with Text in Excel (6 Quick Methods)