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

Home/Excel VBA/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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Conditional Statements in Excel VBA – Download: Example File

Download this example file, we will learn conditional statements with examples.
mongopono.ru 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

LIMITED TIME OFFER
By |July 28th, 2013|Excel VBA|5 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

5 Comments

  1. September 16, 2014 at 2:09 AM - Reply

    I read a lot of interesting articles here. Probably you
    spend a lot of time writing, i know how to save you a lot of time, there is an online tool
    that creates high quality, SEO friendly articles in minutes, just search
    in google – laranitas free content source

  2. Daniel Hill May 6, 2015 at 2:36 AM - Reply

    Can you tell me why this code doesn’t work? I can’t get a For … Next or other versions to work either. For this code I get the following error message “Code execution has been interrupted”. Debug shows the problem is on the iCtr = iCtr + 1 line. Value in Cell(102,4) is dependent on value in cell (102,7).

    iCtr = 1
    Do Until Cells(102, 4) < 100
    Cells(102, 7) = iCtr
    iCtr = iCtr + 1
    Loop

  3. Daniel Hill May 6, 2015 at 2:45 AM - Reply

    Never mind! Found a solution Mahesh Subramaniya at:

    I can add this line of code before the loop:
    Application.EnableCancelKey = xlDisabled

  4. Abhishek June 28, 2015 at 5:07 PM - Reply

    Please add some examples of do until loop..

  5. krishna singh July 26, 2015 at 9:06 PM - Reply

    Hi Sir,

    I am Trying to create a VBA for the Bank reconciliation Statement though some what i have succeed by viewing your clips but stuck in one problem
    if suppose there are 2 sheets such as sheet 1 and sheet 2 . if chq and amount matched so how would we give the sr no in the both sheet which should be same and for the next match it should give other no

    Regards
    Krishna

Leave A Comment


Related pages


excel hide column shortcuteasy excel vbabasics of vbalookup functions excelshortcut key for insert rowhow to enable developer tab in excel 2007xml to xlsx converterusing arrays in vbaexcel hidden columnshow to add the developer tab to the ribbonexcel macro call subhow to remove duplicate values in excelvbyesno in vbahide rows in excel 2010excel vba application.getsaveasfilenameexcel linking cellshow do i unhide a column in excel 2010excel 2003 gantt chart templatevba columnsunhide sheets in excel 2007excel cell vbahow do i merge cells in excel 2013create excel vbaexcel dynamic chart titleactivex controls excelexcel timeline project templatearray lookup excelexcel f7excel vba dim ashow do you unhide columns in excelexcel vba cell valuehow to lock an excel sheet with passwordexcel sort macrosql objective type questions and answers pdfoffset function excel 2013excel trim formulavba excel for loopreplace function in excel 2007vb 6.0 interview questions and answers for experiencedexcel trend analysis templatewhat is the lookup function in excelexcel formula chartsample vb programs with source codeexcel vba current worksheetchart wizard in excel 2007excel.worksheet.cellscreate excel macroexcel formula to find color of celladodb recordset vba excellearn vba accessworkbooks.activatehow to remove excel formulahow to check if there are duplicates in excelvba convertexcel formula is not blankexcel macro to unlock password protected sheetindirect function in vbahow to copy columns to rows in excelsql queries excelhow to unlock the excel sheet forgot the passwordexcel unsharewrite a function in vbavba excel modstep by step pivot table excel 2010how to insert new worksheet in excelexcel cells vbasorting objects worksheetshow to increase the column width in excelmultiple if statements excelvlookup returns naloop vba coderun a macro in excel 2007dll dmlexcel iserror formulacombine multiple excel files into one worksheet