Welcome to Excel Avon
If statement in Excel VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
The IF statement in Excel VBA is used extensively which allows you to make choices. If we compare Excel’s if function with if statement in Excel VBA, then Excel’s if function allows you to make a logical comparison between a value and the output you expect. IF Function is an inbuilt function in Excel which is classified as Logical Function. The VBA IF statement will only perform the first part of the statement, i.e., check whether the condition is true or false, in addition to performing the operations to enter the statement in VBA IF statement.
To understand If Statement in Excel VBA, we have to go to VBE – Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Model has to be inserted, as can be seen in the image.
Only IF Statement in Excel VBA
In this example we will use only if statement in excel vba, First, I will write subroutine for only IF statement.
Sub UseOnlyIf() End Sub
After writing the subroutine, we will define the Response as String
Sub UseOnlyIf() Dim Response as String End Sub
we will define the iRng as range
Sub UseOnlyIf() Dim Response as String Dim iRng as Range End Sub
Define the WS as Worksheet
Sub UseOnlyIf() Dim Response as String Dim iRng as Range Dim WS as Worksheet End Sub
Now I will use Set Function for active sheet.
Sub UseOnlyIf () Dim Response as String Dim iRng as Range Dim WS as Worksheet Set WS = ActiveSheet End Sub
Now Let’s define the response for the range in the worksheet with value function.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS. Range("C2"). Value
End Sub
Again, we will use the set function as we are defining the range for the active sheet and add the Input range.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("C2"). Value
set iRng = WS.Range("H2:H10")
End Sub
Now we will write the first condition and define interior color.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("C2"). Value
set iRng = WS.Range("H2:H10")
If Response = "Yes" Then
irng.Intirior.color = vbred
End Sub
Elseif and we will write second condition for ‘no’ and define the color of the range.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("C2"). Value
set iRng = WS.Range("H2:H10")
If Response = "Yes" Then
irng.Intirior.color = vbred
ElseIf Response = "No" Then
irng.Intirior.color = vbgreen
End Sub
We will write the code for the third condition ‘if the response cell is blank’ and define the color of the range.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("C2"). Value
set iRng = WS.Range("H2:H10")
If Response = "Yes" Then
irng.Intirior.color = vbred
ElseIf Response = "No" Then
irng.Intirior.color = vbgreen
Else
irng.Intirior.color = vbwhite
EndIf
End Sub
After writing the code we will go to the active sheet of excel for the result. We also need to insert a button in our worksheet so that we will be able to run the code.
Assign to macro and give new name of button
We will insert ‘yes’ in the response cell and then click on the button, we will get the result like this
After clicking show result button
After this the second condition which will do ‘no’ in the response cell and click show button.
The last condition that defines the white color in the input range if the response cell is blank.
If Statement with And in Excel VBA
In this example we will use AND function with if statement in excel vba
First, I will write subroutine for IF statement with And
Sub UseIfWithAnd() End Sub
After writing the subroutine, we will define the Response as String
Sub UseIfWithAnd() Dim Response as variant End Sub
we will define the iRng as range
Sub UseIfWithAnd() Dim Response as variant Dim dRng as Range End Sub
Define the WS as Worksheet
Sub UseOnlyIf() Dim Response as String Dim iRng as Range Dim WS as Worksheet End Sub
Now I will use Set Function for active sheet.
Sub UseOnlyIf() Dim Response as String Dim iRng as Range Dim WS as Worksheet Set WS = ActiveSheet End Sub
Now Let’s define the response for the range in the worksheet with value function.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
End Sub
Again, we will use the set function as we are defining the range for the active sheet.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
set dRng = WS.Range("H2")
End Sub
Now I will write First condition with If statement, And Function. Here the first condition is something like this if the value is greater than 0 and less than 10 then the category will be small.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
set dRng = WS.Range("H2")
If response>= 0 and Response <= 10 Then
dRng.Value = "small"
End Sub
Write second condition with ElseIf statement, And Function. Here the second condition is something like this if the value is greater than 11 and less than 20 then the category will be Medium.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
set dRng = WS.Range("H2")
If response>= 0 and Response <= 10 Then
dRng.Value = "small"
ElseIf response>= 11 and Response <= 20 Then
dRng.Value = "Medium"
End Sub
Write Third condition with ElseIf statement, And Function. Here the third condition is something like this if the value is greater than 21 and less than 50 then the category will be Large.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
set dRng = WS.Range("H2")
If response>= 0 and Response <= 10 Then
dRng.Value = "small"
ElseIf response>= 11 and Response <= 20 Then
dRng.Value = "Medium"
ElseIf response>= 11 and Response <= 20 Then
dRng.Value = "Large"
End Sub
Write Fourth condition with ElseIf statement, And Function. Here the fourth condition is something like this if the value is greater than 21 and less than 50 then the category will be Extra Large.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
set dRng = WS.Range("H2")
If response>= 0 and Response <= 10 Then
dRng.Value = "small"
ElseIf response>= 11 and Response <= 20 Then
dRng.Value = "Medium"
ElseIf response>= 21 and Response <= 50 Then
dRng.Value = "Large"
ElseIf response>= 50 Then
dRng.Value = "Extra Large"
End Sub
Write Fourth condition with Else statement. Here the Fifth condition is something like this if the value blank so then category will be blank.
Sub UseOnlyIf()
Dim Response as String
Dim iRng as Range
Dim WS as Worksheet
Set WS = ActiveSheet
Response = WS.Range("G2"). Value
set dRng = WS.Range("H2")
If response>= 0 and Response <= 10 Then
dRng.Value = "small"
ElseIf response>= 11 and Response <= 20 Then
dRng.Value = "Medium"
ElseIf response>= 21 and Response <= 50 Then
dRng.Value = "Large"
ElseIf response>= 50 Then
dRng.Value = "Extra Large"
Else
dRng.Value = ""
End If
End Sub
After writing the code we will go to the active sheet of excel for the result. We also need to put a button in our worksheet so that we can run the code. And you will get the result as you can see in the image.
Now we will change the number click show button and find the category.
And we will write a new integer and click on show result button and then the category will be found as you can see in the image.
If Statement with Or in Excel VBA
In this example we will use OR function with if statement in excel vba To use the if statement with or, two conditions have been created. The condition is such that if one of the cells of number 1 or number 2 is filled, the result will be filled and if both the cells are blank, we will get nothing filled.
to run the code in VBA
First, I will write subroutine for IF statement with Or.
Sub UseIfWithOr() End Sub
After writing the subroutine, we will define respectively the Inp1 and Inp2 as Variant.
Sub UseIfWithOr() Dim Inp1 As Variant Dim Inp2 As Variant End Sub
We will Define WS as Worksheet
Sub UseIfWithOr() Dim Inp1 As Variant Dim Inp2 As Variant Dim WS as Worksheet End Sub
We will Define dRng as Range
Sub UseIfWithOr() Dim Inp1 As Variant Dim Inp2 As Variant Dim WS as Worksheet Dim dRng as Range End Sub
Now I will use Set Function use for Activesheet
Sub UseIfWithOr() Dim Inp1 As Variant Dim Inp2 As Variant Dim WS as Worksheet Dim dRng as Range Set WS = Activesheet End Sub
The range of inp1 and Inp2 is respectively C3 in the active sheet with value function
Sub UseIfWithOr()
Dim Inp1 As Variant
Dim Inp2 As Variant
Dim WS as Worksheet
Dim dRng as Range
Set WS = Activesheet
Inp1=WS.Range("C2").value
Inp2=WS.Range("C3").value
End Sub
Now I will Define Range for result.
Sub UseIfWithOr()
Dim Inp1 As Variant
Dim Inp2 As Variant
Dim WS as Worksheet
Dim dRng as Range
Set WS = Activesheet
Inp1=WS.Range("C2").value
Inp2=WS.Range("C3").value
Set dRng = WS.Range("F2")
End Sub
Now I will write first condition with If Statement. If there is a blank in the first input value or second input value then result will be value filled.
Sub UseIfWithOr()
Dim Inp1 As Variant
Dim Inp2 As Variant
Dim WS as Worksheet
Dim dRng as Range
Set WS = Activesheet
Inp1=WS.Range("C2").value
Inp2=WS.Range("C3").value
Set dRng = WS.Range("F2")
If Inp1 <>"" or Inp2 <>"" Then
drng.value = "value Filled"
End Sub
Now I will write Second condition with If Statement. If both cells are blank, then result will be Nothing filled.
Sub UseIfWithOr()
Dim Inp1 As Variant
Dim Inp2 As Variant
Dim WS as Worksheet
Dim dRng as Range
Set WS = Activesheet
Inp1=WS.Range("C2").value
Inp2=WS.Range("C3").value
Set dRng = WS.Range("F2")
If Inp1 <>"" or Inp2 <>"" Then
drng.value = "value Filled"
Else
drng.Value = " Nothing Filled"
End If
End Sub
After writing the code we will go to the active sheet of excel and click on the Insert button.
We will fill the value in number 1 or number 2 and then click on show result button and then we will get the result
Now we will blank both the cells so that the result will be found
This is how we explained the if statement.
