Excel VBA Else If
VBA Else If allows you to analyze a condition, and perform an action accordingly. IF condition checks if the supplied condition is TRUE or FALSE, if the condition is TRUE it will return the assigned value of Value if True and return Value IF False if the result is FALSE.
The logic of IF condition in regular excel formula & VBA formula is the same. In this article, I will cover complete VBA IF condition.
Syntax of VBA If Statement
First, see the syntax of IF statement in VBA.
This is very similar to our worksheet function IF. The only difference here is we need to put the word THEN to go forward in the function, also Else part of the IF condition is optional unlike in our normal IF condition and We need to mention the end of the function as End If.
Actually, there will be one more argument if the conditions to test are more than one condition and that part is called as ELSE IF statement. This is like our nested IF condition in our worksheet calculations. ELSE IF will come into picture value if the condition is FALSE then we need to test more condition with ELSE IF condition.
In this article, we will see more of ELSE IF condition in the practical world.
How to Use VBA Else If Statement?
Let’s understand how to use VBA Else If Statement with some examples.
Simple If Statement – Example #1
Now theoretical explanation is enough, even if you did not understand anything nothing to worry. In the practical example, you will catch the logic.
Assume you have a value in the cell A2 and you want to check whether the number is greater than 100 or not. If the value is greater than 100 then we need the value in cell B2 as “More than 100”. Below code will perform the required task.
Code:
Sub IF_Example1() If Range("A2").Value > 100 Then Range("B2").Value = "More than 100" End If End Sub
Then run this code using F5 key or manually as shown in the screenshot. Then we can see the result in cell B2.
If you change the value in cell A2 to 99 and run the code. The code will return nothing because the value is less than 100 and we have not supplied any result if the test is FALSE, this we will see in the next example.
If with Else Statement – Example #2
We have seen how single IF with TRUE condition works. Now we will see how to work if the supplied condition is FALSE.
In the current code after the True value is supplied in the next line type word Else.
Code:
Sub IF_Example2() If Range("A2").Value > 100 Then Range("B2").Value = "More than 100" Else End If End Sub
And in the next line write the code for False value.
Code:
Sub IF_Example2() If Range("A2").Value > 100 Then Range("B2").Value = "More than 100" Else Range("B2").Value = "Less than 100" End If End Sub
Then run this code using F5 key or manually as shown in the screenshot. If the value is greater than 100 the result would be “More than 100” in cell B2.
If the value is less than 100 the result would be “Less than 100”.
Nested If Statement with Else If – Example #3
When we want to test more than one condition we need to use more IF statements inside the IF condition. But in VBA we need to use the word ELSE IF to test more than one condition.
For example, in cell A2 if the value is more than 200 we need the result as “More than 200” in cell B1.
If the value is more than 100 we need the result as “More than 100” in cell B2.
If the value is less than 100 we need the result as “Less than 100” in cell B2.
Step 1: After the TRUE value is passed enter the word ELSE IF in the next line.
Code:
Sub IF_Example3() If Range("A2").Value > 200 Then Range("B2").Value = "More than 200" ElseIf Range("A2").Value > 100 Then Range("B2").Value = "More than 100" End If End Sub
Step 2: Since we have already tested two arguments, we are left with only one condition. Now in the next line supply the final result of the test with ELSE statement.
Code:
Sub IF_Example3() If Range("A2").Value > 200 Then Range("B2").Value = "More than 200" ElseIf Range("A2").Value > 100 Then Range("B2").Value = "More than 100" Else Range("B2").Value = "Less than 100" End If End Sub
Step 3: Then run this code using F5 key or manually as shown in the screenshot to see results.
Result 1:
Result 2:
Result 3:
Nested If with Loop – Example #4
This is the advanced example of Nested IF with Loop. Assume you have a sales table with 12 months data.
In the status column, we need the result as follows.
- If the sales value is more than 7000 then the result should be “Excellent”
- If the sales value is more than 6500 then the result should be “Very Good”
- If the sales value is more than 6000 then the result should be “Good”
- If the sales value more than 4000 then the result should be “Not Bad”
- If all the results are FALSE then the result should be “Bad”
In order to perform this test, we need the below code which is a combination of IF with ELSE IF and LOOP.
Code:
Sub IF_Example4() Dim i As Integer i = 2 For i = 2 To 13 If Cells(i, 2).Value >= 7000 Then Cells(i, 3).Value = "Excellent" ElseIf Cells(i, 2).Value >= 6500 Then Cells(i, 3).Value = "Very Good" ElseIf Cells(i, 2).Value >= 6000 Then Cells(i, 3).Value = "Good" ElseIf Cells(i, 2).Value >= 4000 Then Cells(i, 3).Value = "Not Bad" Else Cells(i, 3).Value = "Bad" End If Next i End Sub
Then run this code using F5 key or manually as shown in the screenshot to see results.
Things to Remember
- ELSE IF statement requires the result code in the same line not in the next line and also requires THEN statement to go to the next statement.
- If the END IF statement is not enclosed then we will get the below error.
- The operator <> is nothing but not equal to an IF statement.
- Like worksheet function, we can also use AND & OR statement within IF statement.
Recommended Articles
This has been a guide to VBA Else If Statement. Here we discussed VBA Else If and how to use Excel VBA Else If along with some practical examples and downloadable excel template. You can also go through our other suggested articles –
