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.

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Ultimate Project Management Template
Excel Template

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates

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
ElseIf Mrks < 90 And Mrks >= 80 Then
ElseIf Mrks < 80 And Mrks >= 60 Then
ElseIf Mrks < 60 And Mrks >= 50 Then
ElseIf Mrks < 50 And Mrks >= 35 Then
Else
'Check if the Grade has 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

```

#### 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

Related Resource External VBA Reference