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.

Download Practice Workbook

You can download the practice workbook from here.

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.

Read More: How to Check If Cell Contains One of Several Values in Excel

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.

Read More: How to Prepare IF Statement Contains Multiple Words in Excel

Similar Readings

• How to Use Multiple IF Statements in Excel Data Validation
• Excel IF Statement with VLOOKUP for Multiple Conditions Range
• How to Use If Statement Based on Cell Color in Excel (3 Examples)
• Dynamic Data Validation List in Excel with IF Statement Condition
• How to Use IF Statement with Not Equal To Operator in Excel

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.

Read More: How to Use IF Statement with Yes or No in Excel (3 Examples)

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.

Read More: How to Use IF Function with OR and AND Statement in Excel

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.

Related Articles

• How to Use Conditional Formatting If Statement Is Another Cell
• Excel IF Statement Between Two Numbers (4 Ideal Examples)
• Find Sum If Cell Color Is Green in Excel (4 Easy Methods)
• How to Use Wildcard with If Statement in Excel (5 Methods)
• Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)