How to use if else statement in Excel VBA

excel-vba-if-else-elseif-statement

Excel VBA - If else statement

In Excel VBA if then else statement is used to check for conditions and execute tasks if true and if conditions are not met then a set of codes will be done.

Syntax for if else statement

if(condition) Then
Your code comes here if above condition is true
Elseif(2nd condition) Then
Your code comes here if above 2nd condition is true
Else
Your code comes here if both conditions are not true
EndIF

In this example, we are going to check whether a number is greater than 10 or equal to 10 or less than 10.

Code:

Sub simpleif()
If (Range("A1").Value < 10) Then
Range("B1").Value = "Less than 10"
ElseIf (Range("A1").Value = 10) Then
Range("B1").Value = "Equal to 10"
Else
Range("B1").Value = "Greater than 10"
End If
End Sub

vba-if-else-statement

vba-if-else-statement

Hope by now you might have got some clarity, we will see another example with some complexity.

If else with NOT operator

Syntax for if else with NOT operator

if(condition) AND NOT (condition) Then
Your code executes here if above both conditions are true
Elseif(condition) AND NOT (condition) Then
Your code executes here if above both 2nd conditions are true
Else
Your code comes here if both conditions are not true
EndIF

If else with AND operator

In this example, we are going to assign grades for ten students. The complexity in here is, we will use for loop assign grades at one click.

Syntax for if else with AND operator

if(condition) AND (condition) Then
Your code executes here if above both conditions are true
Elseif(condition) AND (condition) Then
Your code executes here if above both 2nd conditions are true
Else
Your code comes here if both conditions are not true
EndIF

vba-if-else-statement
Code

Dim i As Integer
For i = 2 To 11 //as our marks are starting from 2nd row to 11th we loop from 2 To 11
If (Range("B" & i).Value >= 90) Then
Range("C" & i).Value = "A+"
ElseIf (Range("B" & i).Value >= 80) And (Range("B" & i).Value <= 89) Then
Range("C" & i).Value = "A"
ElseIf (Range("B" & i).Value >= 70) And (Range("B" & i).Value <= 79) Then
Range("C" & i).Value = "B+"
ElseIf (Range("B" & i).Value >= 60) And (Range("B" & i).Value <= 69) Then
Range("C" & i).Value = "B"
ElseIf (Range("B" & i).Value >= 50) And (Range("B" & i).Value <= 59) Then
Range("C" & i).Value = "C+"
ElseIf (Range("B" & i).Value >= 40) And (Range("B" & i).Value <= 49) Then
Range("C" & i).Value = "C"
ElseIf (Range("B" & i).Value >= 35) And (Range("B" & i).Value <= 39) Then
Range("C" & i).Value = "D"
ElseIf (Range("B" & i).Value <= 35) Then
Range("C" & i).Value = "F"
End If
Next i

Output
vba-if-else-statement

If else with OR operator

Unlike we saw in AND operator, this operator executes tasks if either one condition satisfies among two or more. Let us see the syntax for the OR operator.

Syntax for if else with OR operator

if(condition) OR (condition) Then
Your code executes here if above both conditions are true
Elseif(condition) OR (condition) Then
Your code executes here if above both 2nd conditions are true
Else
Your code comes here if both conditions are not true
EndIF

For Example, There are two salesperson A and B, their target is to sell at least 33 products or they must have sold more than the other salesperson ( A > B) or (B > A).

vba-if-else-statement
Code

Dim i As Integer
For i = 2 To 11
If (Range("B" & i).Value > Range("C" & i).Value) Or (Range("B2").Value >= 33) Then
Range("D" & i).Value = "Target Achieved"
Else
Range("D" & i).Value = "Not Achieved"
End If
Next i

Output

In Day 1 A has sold more than B and the second condition is they should sell at least 33 products. As A satisfies either one condition so it prints as Target Achieved".

In Day 2 A has not achieved the target of 33 and also did not sell greater than B. So in this case he met neither the two targets.

vba-if-else-statement

Hope you liked this article, if so please share it with your friends.

If you want more clarity or demo of this code, please watch the below youtube video.

Detailed Video: How to use if else statement in Excel VBA

Share on:
Most Popular