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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of If Then Else Statement Excel


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.

if then else statement dataset overview in excel


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.

Opening the Visual Basic Window in Excel to Insert If Then Else Statement Code

  • Next, go to Insert and click on Module.

Inserting New Module to Write the If Then Else Statement in Excel

  • 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

If Then Else Statement Code to Get Student Grade in Excel

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

Opening Macro List from Developer Tab

  • Then, select the Student_Grade macro and click on Run.

Running the Student_Grade Macro

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

Final Result from the Student_Grade Macro

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

VBA Code to Find Word from Code

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

Running the Word_From_Code VBA Code

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

Word_From_Code Method’s Output

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


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

Code to Comment on the Product Price

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

Running the Product_Price Macro

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

Output of the Product_Price Macro

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

multiple conditions code

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

running the multiple conditions macro

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

inserting a test mark

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

final grade output

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

Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo