WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA If Else Statement

VBA If Else Statement

Excel VBA IF Else Statement

There is no IF Else statement in worksheet and it can be only used in the VBA code, while working in VBA we can provide a condition which is the If condition statement and if it is fulfilled a certain set of instructions then it is executed and if the condition fails the instruction then else statement is executed.

VBA is not different when it comes to logical tests. It works the same way as to how it works in regular worksheets. Of all logical functions, the “IF” function is mostly used from the lot. Using IF, we can conduct a logical test and arrive at the decisions if the logical test is satisfied and also arrive alternative decisions if the logical test is not satisfied.

Below is the syntax of the IF Else condition.

IF <Logical Test> Then
    If the Logical Test is TRUE 
Else
    If the Logical Test is FALSE
End IF

What is VBA IF Then Else Statement?

Once the supplied logical test is FALSE, we need some alternative task to execute as part of the code. So “IF ELSE” means if the logical test is FALSE, what else needs to do.

To better understand the below example, we have supplied the result as “10 is greater” only if the logical test is TRUE. Still, in the FALSE logical result, we can supply the alternative result as “10 is lesser”.

So once the logical tests are supplied and the TRUE part code written in the next line, enter the word “ELSE.”

ELSE means if the logical test is not TRUE, we need the result as “10 is lesser”.

Code:

Sub IF_Else_Example1()

    If 10 > 11 Then
       MsgBox "10 is greater"
    Else
       MsgBox "10 is lesser"
    End If

End Sub

VBA If Else Example 1.1

Now our code gives at least any one of the above results. Execute the code and see the result.

VBA If Else Example 1.2

Since we have supplied the alternative result if the logical test is FALSE, it has displayed the alternative result as “10 is lesser” because 10 is less than the other number 11.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example

You can download this VBA If Else Excel Template here – VBA If Else Excel Template

For example, look at the below data.

VBA If Else Example 2.1

With this data, we need to arrive at the status based on the “Cost” of each product. To arrive status below are the criteria’s.

If the cost price is > 50, the status should be “Expensive,” or else status should be “Not Expensive.”

Here we need to test the cost price, i.e., logical test whether the cost price is >50 or not. If the logical test is TRUE, i.e., the cost price is more than 50, we need the status as “Expensive,” and if the logical test is FALSE, i.e., the cost price is less than 50, we need the alternative result as “Not Expensive.”

Ok, let’s write the code now. Before that, copy and paste the above table to an excel worksheet.

Step 1: Start the subprocedure.

Sub IF_ELSE_Example2()

End Sub

VBA If Else Example 2.2

Step 2: Declare the variable as an Integer data type.

Dim k As Integer

VBA If Else Example 2.3

Step 3: Since we need to test more than one cell values, we need to employ FOR VBA LOOP to loop through the cells and apply the logic for all the cells.

We need to apply logical tests from 2nd row to 8th row, so start FOR LOOP from 2 to 8.

Code:

For k = 2 To 8

Next k

Example 2.4

Step 4: Inside this loop, we need to execute the logical test. So open the IF statement and select the first cell by using CELLS property.

Code:

If Cells(k, 2).Value > 50 Then

Example 2.5

Here Cells(k, 2) means row (value of k) and column 2.

Step 5: If this cell value is > 50, we need the result as “Expensive” in the next column cell. So code will be –

Code:

Cells(k, 3).Value = "Expensive"

Example 2.6

Step 6: If the test isn’t TRUE, we need the ELSE statement results, i.e., “Not Expensive.”

Code:

Sub IF_ELSE_Example2()

    Dim k As Integer

    For k = 2 To 8
        If Cells(k, 2).Value > 50 Then
            Cells(k, 3).Value = "Expensive"
        Else
            Cells(k, 3).Value = "Not Expensive"
        End If
    Next k

End Sub

Example 2.7

This code will loop through from 2nd to 8th-row tests the numbers and arrive at the result based on the cost price.

GIF

Like this, we can use If-Else to arrive for alternative results.

Things to Remember

  • Else statement is for a FALSE logical test.
  • If you want to apply more than two logical tests in excel, we need to use the ELSE IF statement.
  • In the case of task execution for more than one cell, we need to use loops.
  • If Else statement can test only one logical test.

Recommended Articles

This has been a guide to VBA If Else. Here we discuss how to use the If Then Else Statement along with a practical example and downloadable excel template. You may learn more about VBA from the following articles –

  • How to Use Counter in VBA Excel?
  • VBA Asc
  • Boolean in Excel VBA
  • VBA IIF
  • VBA ByRef
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA If Else Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More