How to Use If Then Else Statement in Excel VBA (4 Examples)

In this tutorial, I am going to share with you 4 practical examples to use the If Then Else statement in Excel. You can easily apply these examples in any set of data to conditionally attach appropriate comments. To achieve this task, we will also see some useful features that might come in handy in many other Excel-related tasks. Let’s say a teacher wants to remark on his students’ performances based on their grades. He may effectively use the If Then Else statement to do something like the following image.

Table of Contents hide

Download Practice Workbook

4 Practical Examples to Use If Then Else Statement in Excel

1. Assessment of Students’ Grades (Adding Text Based on a Certain Value with OR Criteria)

2. Extracting Specified Word from Code Based on Condition

3. Commenting on Product Price

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

Things to Remember

Conclusion

Download Practice Workbook

You can download the practice workbook from here.

Use of If Then Else Statement.xlsm

4 Practical Examples to Use If Then Else Statement in Excel

We have taken a concise initial dataset to explain the steps clearly. The dataset has approximately 7 rows and 4 columns. Initially, we are keeping all the cells in General format. For the initial dataset, we have 3 unique columns which are Student Name, Subject, Grade, and Comment. Although we may vary the number of columns later if that is needed.

1. Assessment of Students’ Grades (Adding Text Based on a Certain Value with OR Criteria)

In this first example, we will see how we can quickly generate comments according to the grades obtained by each student using the VBA If Then Else statement in Excel. Follow the steps below for this.

Steps:

First, go to the Developer tab and click on Visual Basic.

Next, go to Insert and click on Module.

Now, insert the following code in the module window:

Sub Student_Grade()
   For Each Sh_grade In Range("D5:D10")
      If Sh_grade = "A" Or Sh_grade = "B" Then
         Sh_grade.Offset(0, 1).Value = "Great Job"
      ElseIf Sh_grade = "C" Then
         Sh_grade.Offset(0, 1).Value = "Not Good"
      Else
         Sh_grade.Offset(0, 1).Value = "Need a Tutor"
      End If
   Next Sh_grade
End Sub

Again, navigate to the Developer tab and click on Macros.

Then, select the Student_Grade macro and click on Run.

As a result, this will automatically give the appropriate comments for each student’s grade.

2. Extracting Specified Word from Code Based on Condition

If we have shortcodes as letters, we can easily use the If Then Else statement in Excel to extract the complete word from that with just a few lines of code. Let us see how to do this.

Steps:

To begin with, open the VBA module window following the steps of Method 1.
Now, enter the code below in the module window.

Sub Word_From_Code()
   Dim Sh_LRegion As String
   Dim Sh_LRegionName As String
   Sh_LRegion = Range("D5").Value
   If Sh_LRegion = "N" Then
      Sh_LRegionName = "North"
   ElseIf Sh_LRegion = "S" Then
      Sh_LRegionName = "South"
   ElseIf Sh_LRegion = "E" Then
      Sh_LRegionName = "East"
   Else
      Sh_LRegionName = "West"
   End If
   Range("E5").Value = Sh_LRegionName
End Sub

Then, open the Macro window following the steps in Method 1.
Next, choose the Word_From_Code macro and click on Run.

Consequently, you should get the full names from the region code values.

Similar Readings

3. Commenting on Product Price

Now in this third example, we will work to attach proper tags on the price of specific products using the If Then Else statement in Excel.

Steps:

Firstly, open the VBA macro module window by following the previous steps of Method 1.
Now, type in the code below inside the module window.

Sub Product_Price()
   For Each Sh_price In Range("D5:D10")
      If Sh_price > 500 Then
         Sh_price.Offset(0, 1).Value = "Overpriced"
      ElseIf Sh_price > 200 And Sh_price <= 500 Then
         Sh_price.Offset(0, 1).Value = "Medium Price"
      Else
         Sh_price.Offset(0, 1).Value = "Lower Priced"
      End If
   Next Sh_price
End Sub

Then, carry out the opening instructions of Method 1 to access the Macro window as before.
After that, select the Word_From_Code macro and press Run.

Finally, you should immediately get the proper comment on the price for the various products.

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

Let us see in this final example, how can we show grades after inputting the obtained marks using the If Then Else statement in Excel.

Steps:

For this, open the VBA module as we did earlier in Method 1.
Next, enter the following code:

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

Now, open the Macro following the steps of Method 1.
Then, select the MultipleConditions macro and click on Run.

After that, insert a sample mark to test and click OK.

Finally, this will quickly give you a message box showing the actual grade value.

Things to Remember

The basic syntax for an ..Then…Else statement in VBA is: “If condition Then statement1 Else statement2”.
Here the condition is a logical expression that evaluates to either True or False.
Statement1 is executed if the condition is True, and the statement2 is executed if the condition is False.
You can have multiple ..Else statements nested within an If…Then…Else statement for more complex logic.
You can also use the ElseIf statement to check multiple conditions within an ..Then…Else statement.
When comparing text strings in VBA, use the = operator or the Like
You can use the And and Or operators to combine multiple conditions in an If
Always use End If to close the If Else statement.

Conclusion

I hope you could apply the methods I showed in this tutorial on how to use the If Then Else statement in Excel. As you can see, there are quite a few ways to achieve this task. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more Excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.