Conditional Statements in Excel VBA – If Else, Case, For, Do While, Do Until, Nested Ifs

Conditional Statements in Excel VBA are very useful in programming, this will give you to perform comparisons to decide or loop through certain number of iterations based on a criteria. In this tutorial we will learn the conditional statements with examples.

In this tutorial:
• » IF Statement
• » If … Else Statement
• » If … ElseIf … Else Statement
• » If … ElseIf … ElseIf Statement
• » Nested If Statement

Conditional Statements in Excel VBA – Download: Example File

Download this example file, we will learn conditional statements with examples.
ANALYSISTABS Examples on Conditional Statements

IF Statement

If .. Then

Syntax:

If <Condition> Then <Statement>

It is a simple Condition to check an expression, if the condition is True it will execute the Statement.

Sub sb1_IFCondition()

     'Check if cell(C2)is greater than 6Lakhs
     If Range("C2") > 600000 Then Range("D2") = Range("C2") * 10 / 100

End Sub

(OR)

Sub sb1_IFCondition()
     
     'Check if cell(C2)is greater than 6Lakhs
     If Range("C2") > 600000 Then        
         Range("D2") = Range("C2") * 10 / 100
     end if

End Sub

It will check if the range C2 value, if it is greater than 600000 it will execute the statement ‘Range(“D2”) = Range(“C2”) * 10 / 100’.

If … Else Statement

Syntax:

If <Condition> Then
   Statements1
Else
   Statements2
End if

It will check the Condition, if the condition is True it will execute the Statements1, if False execute the Statements2.

Example 1: Check whether the cell number is greater than six lakhs

Sub If_Else1()

If Range("B2") > 600000 Then
    Range("D2") = Range("C2") * 5 / 100
Else
    Range("D2") = Range("C2") * 10 / 100
End If

End Sub

Example 2: Check whether the number is even or odd

Sub If_Else2()
    
    'Variable declaration
    Dim Num As Integer
    
    'Accepting the number by the user
    Num = InputBox("Enter the Number:", "Number")
        
    If Num Mod 2 = 0 Then
        'Check whether the number is even
        MsgBox "Entered number is Even."
    Else
        'Check whether the number is odd
        MsgBox "Entered number is Odd."
    End If

End Sub

If … ElseIf … Else Statement

You can use If…ElseIf…Else to check more conditions:

If Condition1 Then
    Statements1
ElseIf Condition2 Then
    Statements2
Else
    StatementsN
End If

Example : Check whether the number entered by the user is positive, negative or equal to zero.

Sub If_ElseIf_Else()
    
    'Variable declaration
    Dim Num As Integer
    
    'Accepting the number by the user
    Num = InputBox("Enter the Number:", "Number")
        
    If Num < 0 Then
        'Check whether the number is less  than zero
        MsgBox "Number is negative."
    ElseIf Num = 0 Then
        'Check whether the number is equal to zero
        MsgBox "Number is zero."
    Else
        'Check whether the number is greater  than zero
        MsgBox "Number is positive."
    End If

End Sub

If … ElseIf … ElseIf Statement

You can use If…ElseIf…ElseIf to check more conditions:

If Condition1 Then
    Statements1
ElseIf Condition2 Then
    Statements2
ElseIf ConditionN Then
    StatementsN
End If

Example : Check whether the number entered by the user is positive, negative or equal to zero.

Sub If_ElseIf_ElseIf()
    
    'Variable declaration
    Dim Num As Integer
    
    'Accepting the number by the user
    Num = InputBox("Enter the Number:", "Number")
        
    If Num < 0 Then
        'Check whether the number is less  than zero
        MsgBox "Number is negative."
    ElseIf Num = 0 Then
        'Check whether the number is equal to zero
        MsgBox "Number is zero."
    ElseIf Num > 0 Then
        'Check whether the number is greater  than zero
        MsgBox "Number is positive."
    End If

End Sub

Nested If Statement

You can use If…ElseIf…ElseIf…ElseIf…Else to check more conditions:

If Condition1 Then
    Statements1
ElseIf Condition2 Then
    Statements2
ElseIf Condition3 Then
    Statements3

    ...........

ElseIf ConditionN Then
    StatementsN
End If

Example 1: Check if the month is fall under which quater using “Nested If” statement and “OR” Operator

Sub NestedIf_FindQuater()
    
    'Variable declaration
    Dim Mnt As String
    
    'Accepting the month by the user
    Mnt = InputBox("Enter the Month:", "Month")
        
    If Mnt = "January" Or Mnt = "February" Or Mnt = "March" Then
        'Check if the month is fall under first quater.
        MsgBox "First Quater."
    ElseIf Mnt = "April" Or Mnt = "May" Or Mnt = "June" Then
        'Check if the month is fall under second quater.
        MsgBox "Second Quater."
    ElseIf Mnt = "July" Or Mnt = "August" Or Mnt = "September" Then
        'Check if the month is fall under third quater.
        MsgBox "Third Quater."
    Else
        'Check if the month is fall under fourth quater.
        MsgBox "Fourth Quater."
    End If

End Sub

Example : Check Student Grade based on Marks using “Nested If” statement and “AND” operator

Sub NestedIf_StudentGrade()
    
    'Variable declaration
    Dim Mrks As String
    
    'Accepting the month by the user
    Mrks = InputBox("Enter the Marks:", "Marks")
        
    If Mrks <= 100 And Mrks >= 90 Then
        'Check if the Grade A++
        MsgBox "Grade : A++"
    ElseIf Mrks < 90 And Mrks >= 80 Then
        'Check if the Grade A+
        MsgBox "Grade : A+"
    ElseIf Mrks < 80 And Mrks >= 60 Then
        'Check if the Grade A
        MsgBox "Grade : A"
    ElseIf Mrks < 60 And Mrks >= 50 Then
        'Check if the Grade B
        MsgBox "Grade : B"
    ElseIf Mrks < 50 And Mrks >= 35 Then
        'Check if the Grade C
        MsgBox "Grade : C"
    Else
        'Check if the Grade has fail
        MsgBox "Grade : Fail"
    End If

End Sub

Select … Case

If you have a more number of conditions to check, the If condition will go through each one of them. The alternative of jumping to the statement that applies to the state of a condition is Select Case.

Syntax:

Select Case Expression
    Case Expression1
	Statement1
    Case Expression2
        Statement2
    Case ExpressionN
        StatementN
End Select 

Following is the example on select case:

Sub sb3_SelectCaseCondition()
Select Case Range("B2")
    Case "D1"
    Range("D2") = Range("C2") * 10 / 100
    Case "D2"
    Range("D2") = Range("C2") * 20 / 100
    Case "D3", "D4"
    Range("D2") = Range("C2") * 15 / 100
    Case Else
    Range("D2") = Range("C2") * 5 / 100
End Select
End Sub

Loops

You can use loops to execute statements certain number of time or until it satisfies some condtion.

For Loop

For loop is useful to execute statements certain number of time.

Syntax:

For CounterVariable = Starting Number To Ending Number
  Statements
Next

The following example show you the message box 5 times with integers

Sub sbForLoop()
Dim iCntr  As Integer
For iCntr = 1 To 5
    msgbox iCntr
Next
End Sub

Following is another Example on For Loop:

Sub sb4_ForLoop()
Dim iCntr  As Integer
For iCntr = 2 To 16
    Cells(iCntr, 4) = Cells(iCntr, 3) * 10 / 100
Next
End Sub

You can use Step statement to stepping the counter.

Sub sbForLoop()
Dim iCntr  As Integer
For iCntr = 1 To 10 Step 2
    msgbox iCntr
Next
End Sub

By default the stepping counter is 1, the below two statements are same:
1. For iCntr = 1 To 10
2. For iCntr = 1 To 10 Step 1

For Each Item In the Loop

If you want to loop through a collection, you can use for each condition. The following example loop through the Sheets collection of the Workbook.

Sub sbForEachLoop()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
    MsgBox Sht.Name
Next
End Sub

Do…While

Do loop is a technique used to repeat an action based on a criteria.

Syntax:

Do While Condition
  Statement(s)
Loop

It will execute the statements if the condition is true,The following is example on Dow While:

Sub sb5_DoWhileLoop()
Dim iCntr  As Integer
iCntr = 2
Do While Cells(iCntr, 3) <> ""
    Cells(iCntr, 4) = Cells(iCntr, 3) * 10 / 100
    iCntr = iCntr + 1
Loop
End Sub

Other flavors of the Do loop:

Do
    Statement(s)
Loop While Condition

'-------------------------------

Do
    Statements
Loop Until Condition

'-------------------------------

Do Until Condition
    Statement(s)
Loop

Exiting in between Loops and Procedure

You can Exit the For loop in between based on a condition using Exit For

In the following, it will exit the is for loop if the sheet name is equals to “Data”

Sub sbForEachLoop()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
    MsgBox Sht.Name
    if Sht.Name="Data" then Exit For
Next
End Sub

You can Exit any Procedure using Exit Sub

In the following, it will exit the procedure if the sheet name is equals to “Data”

Sub sbForEachLoop()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
    MsgBox Sht.Name
    if Sht.Name="Data" then Exit Sub
Next
End Sub

You can Exit Exiting a Do Loop using Exit Do