This post provides a complete guide to the VBA If Statement in VBA. If you are looking for the syntax then check out the quick guide in the first section which includes some examples.
The table of contents below provides an overview of what is included in the post. You use this to navigate to the section you want or you can read the post from start to finish.
“Guess, if you can, and choose, if you dare.” – Pierre Corneille
Contents
- 1 Quick Guide to the VBA If Statement
- 2 The Webinar
- 3 What is the VBA If Statement
- 4 The Test Data and Source Code
- 5 Format of the VBA If-Then Statement
- 6 A Simple If Then Example
- 7 Using Conditions with the VBA If Statement
- 8 Using ElseIf with the VBA If Statement
- 9 Using Else With the VBA If Statement
- 10 Using Logical Operators with the VBA If Statement
- 11 The IIF function
- 12 Using Select Case
- 13 Try this VBA If Statement Exercise
- 14 What’s Next?
Quick Guide to the VBA If Statement
Description | Format | Example |
---|---|---|
If Then | If [condition is true] Then [do something] End If | If score = 100 Then Debug.Print "Perfect" End If |
If Else | If [condition is true] Then [do something] Else [do something] End If | If score = 100 Then Debug.Print "Perfect" Else Debug.Print "Try again" End If |
If ElseIf | If [condition 1 is true] Then [do something] ElseIf [condition 2 is true] Then [do something] End If | If score = 100 Then Debug.Print "Perfect" ElseIf score > 50 Then Debug.Print "Passed" ElseIf score <= 50 Then Debug.Print "Try again" End If |
Else and ElseIf (Else must come after ElseIf's) | If [condition 1 is true] Then [do something] ElseIf [condition 2 is true] Then [do something] Else [do something] End If | If score = 100 Then Debug.Print "Perfect" ElseIf score > 50 Then Debug.Print "Passed" ElseIf score > 30 Then Debug.Print "Try again" Else Debug.Print "Yikes" End If |
If without Endif (One line only) | If [condition is true] Then [do something] | If value <= 0 Then value = 0 |
The following code shows a simple example of using the VBA If statement
If Sheet1.Range("A1").Value > 5 Then Debug.Print "Value is greater than five." ElseIf Sheet1.Range("A1").Value < 5 Then Debug.Print "value is less than five." Else Debug.Print "value is equal to five." End If
The Webinar
Members of the Webinar Archives can access the webinar for this article by clicking on the image below.
(Note: Website members have access to the full webinar archive.)
What is the VBA If Statement
The VBA If statement is used to allow your code to make choices when it is running.
You will often want to make choices based on the data your macros reads.
For example, you may want to read only the students who have marks greater than 70. As you read through each student you would use the If Statement to check the marks of each student.
The important word in the last sentence is check. The If statement is used to check a value and then to perform a task based on the results of that check.
The Test Data and Source Code
We’re going to use the following test data for the code examples in this post:
You can download the test data with all the source code for post plus the solution to the exercise at the end:
Format of the VBA If-Then Statement
The format of the If Then statement is as follows
If [condition is true] Then
The If keyword is followed by a Condition and the keyword Then
Every time you use an If Then statement you must use a matching End If statement.
When the condition evaluates to true, all the lines between If Then and End If are processed.
If [condition is true] Then [lines of code] [lines of code] [lines of code] End If
To make your code more readable it is good practice to indent the lines between the If Then and End If statements.
Indenting Between If and End If
Indenting simply means to move a line of code one tab to the right. The rule of thumb is to indent between start and end statements like
Sub … End Sub
If Then … End If
If Then… ElseIf … Else … Endif
For … Next
Do While … Loop
Select Case … End Case
To indent the code you can highlight the lines to indent and press the Tab key. Pressing Shift + Tab will Outdent the code i.e. move it one tab to the left.
You can also use the icons from the Visual Basic Toolbar to indent/outdent the code

Select code and click icons to indent/outdent
If you look at any code examples on this website you will see that the code is indented.
A Simple If Then Example
The following code prints out the names of all students with marks greater than 50 in French.
' https://excelmacromastery.com/ Sub ReadMarks() Dim i As Long ' Go through the marks columns For i = 2 To 11 ' Check if marks greater than 50 If Sheet1.Range("C" & i).Value > 50 Then ' Print student name to the Immediate Window(Ctrl + G) Debug.Print Sheet1.Range("A" & i).Value & " " & Sheet1.Range("B" & i).Value End If Next End Sub
Results
Bryan Snyder
Juanita Moody
Douglas Blair
Leah Frank
Monica Banks
Play around with this example and check the value or the > sign and see how the results change.
Using Conditions with the VBA If Statement
The piece of code between the If and the Then keywords is called the condition. A condition is a statement that evaluates to true or false. They are mostly used with Loops and If statements. When you create a condition you use signs like >,<,<>,>=,<=,=.
The following are examples of conditions
Condition | This is true when |
---|---|
x < 5 | x is less than 5 |
x <= 5 | x is less than or equal to 5 |
x > 5 | x is greater than 5 |
x >= 5 | x is greater than or equal to 5 |
x = 5 | x is equal to 5 |
x <> 5 | x does not equal 5 |
x > 5 And x < 10 | x is greater than 5 AND x is less than 10 |
x = 2 Or x >10 | x is equal to 2 OR x is greater than 10 |
Range("A1") = "John" | Cell A1 contains text "John" |
Range("A1") <> "John" | Cell A1 does not contain text "John" |
You may have noticed x=5 as a condition. This should not be confused with x=5 when used as an assignment.
When equals is used in a condition it means “is the left side equal to the right side”.
The following table demonstrates how the equals sign is used in conditions and assignments
Using Equals | Statement Type | Meaning |
---|---|---|
Loop Until x = 5 | Condition | Is x equal to 5 |
Do While x = 5 | Condition | Is x equal to 5 |
If x = 5 Then | Condition | Is x equal to 5 |
For x = 1 To 5 | Assignment | Set the value of x to 1, then to 2 etc. |
x = 5 | Assignment | Set the value of x to 5 |
b = 6 = 5 | Assignment and Condition | Assign b to the result of condition 6 = 5 |
x = MyFunc(5,6) | Assignment | Assign x to the value returned from the function |
The last entry in the above table shows a statement with two equals. The first equals sign is the assignment and any following equals signs are conditions.
This might seem confusing at first but think of it like this. Any statement that starts with a variable and an equals is in the following format
[variable] [=] [evaluate this part]
So whatever is on the right of the equals sign is evaluated and the result is placed in the variable. Taking the last three assignments again, you could look at them like this
[x] [=] [5]
[b] [=] [6 = 5]
[x] [=] [MyFunc(5,6)]
Using ElseIf with the VBA If Statement
The ElseIf statement allows you to choose from more than one option. In the following example we print for marks that are in the Distinction or High Distinction range.
' https://excelmacromastery.com/ Sub UseElseIf() If Marks >= 85 Then Debug.Print "High Destinction" ElseIf Marks >= 75 Then Debug.Print "Destinction" End If End Sub
The important thing to understand is that order is important. The If condition is checked first.
If it is true then “High Distinction” is printed and the If statement ends.
If it is false then the code moves to the next ElseIf and checks it condition.
Let’s swap around the If and ElseIf from the last example. The code now look like this
' https://excelmacromastery.com/ Sub UseElseIfWrong() ' This code is incorrect as the ElseIf will never be true If Marks >= 75 Then Debug.Print "Destinction" ElseIf Marks >= 85 Then ' code will never reach here Debug.Print "High Destinction" End If End Sub
In this case we check for a value being over 75 first. We will never print “High Distinction” because if a value is over 85 is will trigger the first if statement.
To avoid these kind of problems we should use two conditions. These help state exactly what you are looking for a remove any confusion. The example below shows how to use these. We will look at more multiple conditions in the section below.
If marks >= 75 And marks < 85 Then Debug.Print "Destinction" ElseIf marks >= 85 And marks <= 100 Then Debug.Print "High Destinction" End If
Let’s expand the original code. You can use as many ElseIf statements as you like. We will add some more to take into account all our mark classifications.
If you want to try out these examples you can download the code from the top of this post.
Using Else With the VBA If Statement
The VBA Else statement is used as a catch all. It basically means “if no conditions were true” or “everything else”. In the previous code example, we didn’t include a print statement for a fail mark. We can add this using Else.
' https://excelmacromastery.com/ Sub UseElse() If Marks >= 85 Then Debug.Print "High Destinction" ElseIf Marks >= 75 Then Debug.Print "Destinction" ElseIf Marks >= 55 Then Debug.Print "Credit" ElseIf Marks >= 40 Then Debug.Print "Pass" Else ' For all other marks Debug.Print "Fail" End If End Sub
So if it is not one of the other types then it is a fail.
Let’s write some code to go through our sample data and print the student and their classification:
' https://excelmacromastery.com/ Sub AddClass() ' get the last row Dim startRow As Long, lastRow As Long startRow = 2 lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row Dim i As Long, Marks As Long Dim sClass As String ' Go through the marks columns For i = startRow To lastRow Marks = Sheet1.Range("C" & i).Value ' Check marks and classify accordingly If Marks >= 85 Then sClass = "High Destinction" ElseIf Marks >= 75 Then sClass = "Destinction" ElseIf Marks >= 55 Then sClass = "Credit" ElseIf Marks >= 40 Then sClass = "Pass" Else ' For all other marks sClass = "Fail" End If ' Write out the class to column E Sheet1.Range("E" & i).Value = sClass Next End Sub
The results look like this with column E containing the classification of the marks
Remember that you can try these examples for yourself with the code download from the top of this post.
Using Logical Operators with the VBA If Statement
You can have more than one condition in an If Statement. The VBA keywords And and Or allow use of multiple conditions.
These words work in a similar way to how you would use them in English.
Let’s look at our sample data again. We now want to print all the students that got over between 50 and 80 marks.
We use And to add an extra condition. The code is saying: if the mark is greater than or equal 50 and less than 75 then print the student name.
' https://excelmacromastery.com/ Sub CheckMarkRange() Dim i As Long, marks As Long For i = 2 To 11 ' Store marks for current student marks = Sheet1.Range("C" & i).Value ' Check if marks greater than 50 and less than 75 If marks >= 50 And marks < 80 Then ' Print first and last name to Immediate window(Ctrl G) Debug.Print Sheet1.Range("A" & i).Value & Sheet1.Range("B" & i).Value End If Next End Sub
Results
Douglas Blair
Leah Frank
Monica Banks
In our next example we want the students who did History or French. So in this case we are saying if the student did History OR if the student did French:
' Description: Uses OR to check the study took History or French. ' Worksheet: Marks ' Output: Result are printed to the Immediate Windows(Ctrl + G) ' https://excelmacromastery.com/vba-if Sub UseOr() ' Get the data range Dim rg As Range Set rg = shMarks.Range("A1").CurrentRegion Dim i As Long, subject As String ' Read through the data For i = 2 To rg.Rows.Count ' Get the subject subject = rg.Cells(i, 4).Value ' Check if subject greater than 50 and less than 80 If subject = "History" Or subject = "French" Then ' Print first name and subject to Immediate window(Ctrl G) Debug.Print rg.Cells(i, 1).Value & " " & rg.Cells(i, 4).Value End If Next End Sub
Results
Bryan History
Bradford French
Douglas History
Ken French
Leah French
Rosalie History
Jackie History
Using Multiple conditions like this is often a source of errors. The rule of thumb to remember is to keep them as simple as possible.
Using If And
The AND works as follows
Condition 1 | Condition 2 | Result |
TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE |
FALSE | TRUE | FALSE |
FALSE | FALSE | FALSE |
What you will notice is that AND is only true when all conditions are true
Using If Or
The OR keyword works as follows
Condition 1 | Condition 2 | Result |
TRUE | TRUE | TRUE |
TRUE | FALSE | TRUE |
FALSE | TRUE | TRUE |
FALSE | FALSE | FALSE |
What you will notice is that OR is only false when all the conditions are false.
Mixing AND and OR together can make the code difficult to read and lead to errors. Using parenthesis can make the conditions clearer.
' https://excelmacromastery.com/ Sub OrWithAnd() Dim subject As String, marks As Long subject = "History" marks = 5 If (subject = "French" Or subject = "History") And marks >= 6 Then Debug.Print "True" Else Debug.Print "False" End If End Sub
Using If Not
There is also a NOT operator. This returns the opposite result of the condition.
Condition | Result |
TRUE | FALSE |
FALSE | TRUE |
The following two lines of code are equivalent.
If marks < 40 Then If Not marks >= 40 Then
as are
If True Then If Not False Then
and
If False Then If Not True Then
Putting the condition in parenthesis makes the code easier to read
If Not (marks >= 40) Then
A common usage of Not when checking if an object has been set. Take a worksheet for example. Here we declare the worksheet
Dim mySheet As Worksheet ' Some code here
We want to check mySheet is valid before we use it. We can check if it is nothing.
If mySheet Is Nothing Then
There is no way to check if it is something as there is many different ways it could be something. Therefore we use Not with Nothing
If Not mySheet Is Nothing Then
If you find this a bit confusing you can use parenthesis like this
If Not (mySheet Is Nothing) Then
The IIF function
Note that you can download the IIF examples below and all source code from the top of this post.
VBA has an fuction similar to the Excel If function. In Excel you will often use the If function as follows:
=IF(F2=””,””,F1/F2)
The format is
=If(condition, action if true, action if false).
VBA has the IIf statement which works the same way. Let’s look at an example. In the following code we use IIf to check the value of the variable val. If the value is greater than 10 we print true otherwise we print false:
' Description: Using the IIF function to check a number. ' Worksheet: Marks ' Output: Result are printed to the Immediate Windows(Ctrl + G) ' https://excelmacromastery.com/vba-if Sub CheckNumberIIF() Dim result As Boolean Dim number As Long ' Prints True number = 11 result = IIf(number > 10, True, False) Debug.Print "Number " & number & " greater than 10 is " & result ' Prints false number = 5 result = IIf(number > 10, True, False) Debug.Print "Number " & number & " greater than 10 is " & result End Sub
In our next example we want to print out Pass or Fail beside each student depending on their marks. In the first piece of code we will use the normal VBA If statement to do this:
' https://excelmacromastery.com/ Sub CheckMarkRange() Dim i As Long, marks As Long For i = 2 To 11 ' Store marks for current student marks = Sheet1.Range("C" & i).Value ' Check if student passes or fails If marks >= 40 Then ' Write out names to to Column F Sheet1.Range("E" & i) = "Pass" Else Sheet1.Range("E" & i) = "Fail" End If Next End Sub
In the next piece of code we will use the IIf function. You can see that the code is much neater here:
' Description: Using the IIF function to check marks. ' Worksheet: Marks ' Output: Result are printed to the Immediate Windows(Ctrl + G) ' https://excelmacromastery.com/vba-if Sub CheckMarkRange() ' Get the data range Dim rg As Range Set rg = shMarks.Range("A1").CurrentRegion Dim i As Long, marks As Long, result As String ' Go through the marks columns For i = 2 To rg.Rows.Count ' Store marks for current student marks = rg.Cells(i, 3).Value ' Check if student passes or fails result = IIf(marks >= 40, "Pass", "Fail") ' Print the name and result Debug.Print rg.Cells(i, 1).Value, result Next End Sub
You can see the IIf function is very useful for simple cases where you are dealing with two possible options.
Using Nested IIf
You can also nest IIf statements like in Excel. This means using the result of one IIf with another. Let’s add another result type to our previous examples. Now we want to print Distinction, Pass or Fail for each student.
Using the normal VBA we would do it like this
' https://excelmacromastery.com/ Sub CheckResultType2() Dim i As Long, marks As Long For i = 2 To 11 ' Store marks for current student marks = Sheet1.Range("C" & i).Value If marks >= 75 Then Sheet1.Range("E" & i).Value = "Distinction" ElseIf marks >= 40 Then ' Write out names to to Column F Sheet1.Range("E" & i).Value = "Pass" Else Sheet1.Range("E" & i).Value = "Fail" End If Next End Sub
Using nested IIfs we could do it like this:
' Description: Using a nested IIF function to check marks. ' Worksheet: Marks ' Output: Result are printed to the Immediate Windows(Ctrl + G) ' https://excelmacromastery.com/vba-if Sub UsingNestedIIF() ' Get the data range Dim rg As Range Set rg = shMarks.Range("A1").CurrentRegion Dim i As Long, marks As Long, result As String ' Go through the marks columns For i = 2 To rg.Rows.Count marks = rg.Cells(i, 3).Value result = IIf(marks >= 55, "Credit", IIf(marks >= 40, "Pass", "Fail")) Debug.Print marks, result Next i End Sub
Using nested IIf is fine in simple cases like this. The code is simple to read and therefore not likely to have errors.
What to Watch Out For
It is important to understand that the IIf function always evaluates both the True and False parts of the statement regardless of the condition.
In the following example we want to divide by marks when it does not equal zero. If it equals zero we want to return zero.
marks = 0 total = IIf(marks = 0, 0, 60 / marks)
However, when marks is zero the code will give a “Divide by zero” error. This is because it evaluates both the True and False statements. The False statement here i.e. (60 / Marks) evaluates to an error because marks is zero.
If we use a normal IF statement it will only run the appropriate line.
marks = 0 If marks = 0 Then 'Only executes this line when marks is zero total = 0 Else 'Only executes this line when marks is Not zero total = 60 / marks End If
What this also means is that if you have Functions for True and False then both will be executed. So IIF will run both Functions even though it only uses one return value. For example
'Both Functions will be executed every time total = IIf(marks = 0, Func1, Func2)
(Thanks to David for pointing out this behaviour in the comments)
If Versus IIf
So which is better?
You can see for this case that IIf is shorter to write and neater. However if the conditions get complicated you are better off using the normal If statement. A disadvantage of IIf is that it is not well known so other users may not understand it as well as code written with a normal if statement.
Also as we discussed in the last section IIF always evaluates the True and False parts so if you are dealing with a lot of data the IF statement would be faster.
My rule of thumb is to use IIf when it will be simple to read and doesn’t require function calls. For more complex cases use the normal If statement.
Using Select Case
The Select Case statement is an alternative way to write an If statment with lots of ElseIf’s. You will find this type of statement in most popular programming languages where it is called the Switch statement. For example Java, C#, C++ and Javascript all have a switch statement.
The format is
Select Case [variable] Case [condition 1] Case [condition 2] Case [condition n] Case Else End Select
Let’s take our AddClass example from above and rewrite it using a Select Case statement.
' https://excelmacromastery.com/ Sub AddClass() ' get the last row Dim startRow As Long, lastRow As Long startRow = 2 lastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row Dim i As Long, Marks As Long Dim sClass As String ' Go through the marks columns For i = startRow To lastRow Marks = Sheet1.Range("C" & i).Value ' Check marks and classify accordingly If Marks >= 85 Then sClass = "High Destinction" ElseIf Marks >= 75 Then sClass = "Destinction" ElseIf Marks >= 55 Then sClass = "Credit" ElseIf Marks >= 40 Then sClass = "Pass" Else ' For all other marks sClass = "Fail" End If ' Write out the class to column E Sheet1.Range("E" & i).Value = sClass Next End Sub
The following is the same code using a Select Case statement. The main thing you will notice is that we use “Case 85 to 100” rather than “marks >=85 And marks <=100”.
' https://excelmacromastery.com/ Sub AddClassWithSelect() ' get the first and last row Dim firstRow As Long, lastRow As Long firstRow = 2 lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row Dim i As Long, marks As Long Dim sClass As String ' Go through the marks columns For i = firstRow To lastRow marks = Sheet1.Range("C" & i).Value ' Check marks and classify accordingly Select Case marks Case 85 To 100 sClass = "High Destinction" Case 75 To 84 sClass = "Destinction" Case 55 To 74 sClass = "Credit" Case 40 To 54 sClass = "Pass" Case Else ' For all other marks sClass = "Fail" End Select ' Write out the class to column E Sheet1.Range("E" & i).Value = sClass Next End Sub
Using Case Is
You could rewrite the select statement in the same format as the original ElseIf. You can use Is with Case.
' https://excelmacromastery.com/ Select Case marks Case Is >= 85 sClass = "High Destinction" Case Is >= 75 sClass = "Destinction" Case Is >= 55 sClass = "Credit" Case Is >= 40 sClass = "Pass" Case Else ' For all other marks sClass = "Fail" End Select
You can use Is to check for multiple values. In the following code we are checking if marks equals 5, 7 or 9.
' https://excelmacromastery.com/ Sub TestMultiValues() Dim marks As Long marks = 7 Select Case marks Case Is = 5, 7, 9 Debug.Print True Case Else Debug.Print False End Select End Sub
Try this VBA If Statement Exercise
We covered a lot in this post about the If statement. A good way to help you understand it is by trying to write some code using the topics we covered. The following exercise uses the test data from this post. The answer to the exercise is below.
Click Here to Download the Test Data
The cell G1 contains the name of a subject. We can change this manually.
When our application runs it should write out details of students who have taken the subject entered in cell G1.
We want to classify their result as pass or fail. Marks below 40 is a fail and marks 40 or above is a pass.
Our results will be enter in columns like this:
Column H: First name
Column I: Second name
Column J: Marks
Column K: Subject
Column L: Result type – Pass or Fail
If cell G1 contains “French” then your result should look like this:
Answer to the Exercise
The solution for this exercise is avaible as part of the source code download below:
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Excellent site, thanks Paul!
You might want to include a warning about IIF’s nasty practice of fully evaluating both the True and False part before selecting the appropriate one. This is bad enough when you get an error (e.g. IIF(1=1,99/1,99/0) always results in a Divide by Zero), but it can be a lot trickier when Functions are involved!
Thanks David.
Your VBA helping website is truly looking great. Great work you have done. Congrats!
Narendra
Hi, I have to write an IF statement when 4 out of 6 conditions are true (4 out of 6 months have a certain condition). Is there a way to combine it to say that If (for example) 4 out of 6 months are = 1.3* a cell, then do this?
Your website is very helpful; thank you!
Hi Mary if the six months are in a range of cells you could use CountIf like this
otherwise a loop is the most efficient way
Type Duration
Other 1:43:08
Break 0:52:44
BB_Bid_Lead 0:16:18
BB_Bid_Lead 0:20:16
BB_Bid_Lead 0:20:16
BB_Bid_Lead 0:12:11
BB_Bid_Lead 0:18:21
Other 1:24:26
BB_Bid_Lead 0:12:48
BB_Bid_Lead 0:10:30
Other 0:23:25
BB_Bid_Lead 0:18:16
BB_Bid_Lead 0:11:36
I am looking for a code for example if “BB_Bid_Lead ” then I will get the sumtotal of duration
you can use WorksheetFunction.SumIf()
I am looking for VBA code , if range(left(“a”,3)&z) =”x “then,
range (“b”&z) =”zz”
Please help me on this code. Thanks
Do you mean
If Left(Range(“A” & z).Value,3) = “x” Then
Thank you so much for your explanation. I was struck with doing an if loop macro for a long time for my job. Now its a success.
Hello Paul,
I need a IF formula with the different sheets.
i.e. If sheet2 on B1 column has the amount of “100” then it is “TRUE” then
If it is false it is “False”.
Please give a formula for this on VBA.
Thanks & Regards,
Naresh.K
As i’m writing the VBA in Sheet1..
If sheets(2).Range(“B1”)= 100 Then
sheets(1).Range(“A1”)= = “True”
else
sheets(1).Range(“A1”)= = “False”
End if
Hello,
i need formula for following data,
as
A1 = Title; B1 = start date; C1 = end date; D1=01-jan-18; E1=01-feb-18; F1=01-mar-18; G1=01-apr-18; H1=01-may-18;
A2 = Equipment-01;
A3 = Equipment-02;
A4 = Equipment-03;
A5 = Equipment-04;
if i enter number in D3=1; E3=1; F3=1; G3=1;
i need B3=D2 value (use of staring date) & C3 = G2 Value (use of last used date)
same follow all the equipments.
I hope you understand my requirement, and I awaiting for your reply.
Thanks.
hi Paul
Thanks for the very clear explanations. I have enjoyed this, and learnt a lot. One question, if I may:
How does the first use of If work below, when used in a data dictionary
With ColorStack
If .Count Then ‘this use of If .count doesn’t appear to challenge anything. I’m confused. Help.
Pop = ColorStack(.Count)
.Remove .Count
End If
End With
Hi Les,
If the result of the condition is true or false it doesn’t need to be compared to anything.
These are the same
If A = True Then
If A Then
If the condition results in zero it is false. Any other value is true so
If .Count Then
is the same as
If .Count <> 0 Then
Paul
Hi Paul,
I need below conditions.
Cell A1: 303
Cell A2: 303,271
Cell A3: 275,471
Cell A4: 471,303,797
Cell A5: 275,303
I need to sear all 303 rows by using autofilter conditions. Could you please help on this.
hi, i hope this is related enough to ask.. i am not that good at vb & if worded correct:
– how do you add a boolean test to a vb, where i need a ‘byref’ as part of that boolean eg:
If FX(“DD”) Then ‘example below for doing a FIND of characters in a cell. need “y” variable for eg: “DD”
for eg:
Function isA(ByRef x As Range): On Error Resume Next: isA = 0 ‘portions here hope enough
Dim a As String, findX As Range, rng As Integer: rng = x.row: a = Cells(rng, D2).Value
dim findX As Range, rng As Integer: rng = x.row ‘PROBLEM LINE BELOW: (Y as string not work)
‘FX(y as string) As Boolean: Set findX = x.find(y, LookAt:=xlPart, MatchCase:=True): If Not findX Is Nothing Then FX = True ‘where i have an external macro this very same thing (is unreliable/ vb loses its place? with wacky results) for:
If FX(“DD”) Then If a > 3 And a < 7 then isA=2 else isA=1
end function 'otherwise, the syntax correct? but funny results happen from the external macro below:
Function FXeg(ByRef y As String) As Boolean: Dim x As Range, findX As Range, K5 As String: K5 = Range("K5"): Set x = Cells(activecell.row, K5): FXeg = False 'VB call: fx("anyword")
Set findX = x.find(y, LookAt:=xlPart, MatchCase:=True): If Not findX Is Nothing Then FXeg = True
End Function 'FINDX 'YES this works? remoting argument out of main vb causes vb to lose its place & fail?
'note: K5 is a work cell showing eg: CY:CY
Hi Dave,
I’m not clear on what you are looking for. I would avoid joining lines of code with the colon. It makes the code difficult to read and follow.
I tried to run the code but it is missing End If statements. Can you give me a simpler and clearer example?
Paul
Hi,
I am trying to do 2 things. I want it so that when you click on the Quarterly check box, the button below will apply to the main sheet (FormB) and sheet 2 (FormAquart). I also want it so that when you click on the Semi-Annual check box that the code below applies to the main sheet and sheet 3(FormASemiannual). Do you have any insight on how to do so? At the moment, the code below lets you click on the ADD Row button and both sheets 2 and 3 duplicate the additional rows and pastes the formulas and format. I just need it so that the conditions change based on whether you click on Quarterly or Semi-Annual. Thanks
Sub AddRow_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim mySheets
Dim i As Long
mySheets = Array(“formb”, “formaquart”, “formasemiannual”)
For i = LBound(mySheets) To UBound(mySheets)
With Sheets(mySheets(i))
.Range(“A28”).EntireRow.Insert Shift:=xlDown
End With
With Sheets(mySheets(i))
.Range(“A28:P28”).FillDown
End With
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
You should set up two events – one for when each checkbox is clicked.
Call AddRow with the appropriate worksheet as parameter.
AddRow(sh as worksheet)
Hi Paul,
I am using excel 2013. I am new to VBA coding. This is my first macro. I am stuck in one step. I have thousands of invoice numbers in column:A with different prefixes(ie:908, 958, 904.etc). I want to name different invoice types with different names in column:B. But I am unable to figure it out how to write this. Please help. I sow your reply in the below site for different question. https://excelmacromastery.com/vba-if/
Please Mr Paul, I want to create a search box and a button to click, and when I type a number on the search box and click on the button it will loop through all my worksheets and open the worksheet where the data or number is found.
I will be very grateful if I can get the code that can do this. Thanks and God bless you for your extra ordinary effort in teaching people how to code in Excel VBA.
Godday From Nigeria.
Is it possible to refer to a variable’s value if that variable name is in your data? IE (very over simplified), in cells 1,1 I have the word “SuperMan”. I have a variable; Dim SuperMan as String; SuperMan = “Big red S on his chest”. I would like to have a procedure that goes down the rows (for each, or for to, don’t really care) and adds in the variable’s values based on the values in column 1. I can get it to say SuperMan, and in the immediate window I can see that SuperMan = “Big red S on his chest”, but I can’t get that value into cells 1,2. unless I specifically type “SuperMan” in the coding, I can’t reference cells 1,1 to return the variables value.
You could use a Dictionary. Make “Superman” the key and the other text the value.
Paul
That would work if SuperMan was manually entered into the dictionary along with the “Big red S on his chest”. like dict.Add Superman, “Big Red S on his Chest”. But dict.Add (cells,1,1).Value, Cells(1,1) doesn’t work. It will just be Superman as the key and superman as the item never referring to the global variable that says Superman = Big red S on his chest
I meant you could use the dictionary instead of the global variable.
What you are looking for is Reflection which doesn’t exist in VBA. But if you google it you can see there are some workarounds.
Hi Mr. Kelly
Please help me
I have this code:
If Range(“A1”).Value = “1” Then
OptionButton1.Visible = True
Else
OptionButton1.Visible = False
it is working but when “A1” has a formula that equal “1” it is not working…
Tnx
Put a breakpoint on the If line and check the value of Range(“A1”).Value
Hi below is a script that is stopping at;
If Cells(x, “C”) = “”
and will not go further. Can you help?
Sub NewQuoteNumber()
Range(“t7:t9″).Select
Selection.Copy
Workbooks.Open Filename:=”Q:\Company Shared Folders\Fabrication\SW\Quote Register.xls”, _
UpdateLinks:=0
Range(“E1”).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
For x = 7 To 9999
If Cells(x, “C”) = “” Then y = x: GoTo Bingo
Next x
Bingo:
Cells(y, “C”) = Cells(1, “E”)
Cells(y, “B”) = Cells(2, “E”)
Cells(y, “D”) = Cells(3, “E”)
Cells(y, “A”).Select
Selection.Copy
Windows(“Curtin Quote Sheeta.xls”).Activate
Range(“E7”).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows(“Quote Register.xls”).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Hi Paul,
Is there an error message when it stops?
Also when it stops you can open the watch window(View->Watch Window from menu) and highling and drag Cells(x, “C”) to the watch window.
This will show the value in the cell.
The “Marks” code above works great for my use, but I’m having trouble when I try to convert “Debug.Print” to an output cell. Here’s what I have — the commented items at the bottom are what I’ve tried, unsuccessfully. Please forgive the overly detailed commenting — I have a poor memory and really rely on the “breadcrumbs.”
PS I just signed up for the classes today — the articles on the If statement, the Dim, and Error Handling alone were worth the price of admission. You’re saving me!
Thanks!
Sub InvPeriod_and_IP_Start()
Dim startRow As Long
Dim lastRow As Long
Dim i As Long ‘counter to loop through cells
Dim invPd As String ‘text of the cell contents
Dim invPdStart As String ‘ text of the cell contents
Dim newString As String
‘ go to the first cell and insert a new column to the right of the working columns (new column AL – temporary)
Range(“AK1”).Select
ActiveCell.EntireColumn.Offset(0, 1).Insert
Range(“AJ2”).Select
startRow = 2
lastRow = Sheets(“owssvr”).Cells(Sheets(“owssvr”).Rows.Count, 1).End(xlUp).Row
For i = startRow To lastRow
invPd = Sheets(“owssvr”).Range(“AJ” & i).Value
invPdStart = Sheets(“owssvr”).Range(“AK” & i).Value
‘ if both InvestmentPeriod and InvPeriodStart have content, do the next line
If invPd “” And invPdStart “” Then
‘ print joined expresion to the Immediate window
Debug.Print Worksheets(“owssvr”).Range(“AJ” & i).Value; ” months (” & Worksheets(“owssvr”).Range(“AK” & i).Value; “)”
‘newString = Sheets(“owssvr”).Range(“AJ” & i).Value; ” months (” & Sheets(“owssvr”).Range(“AK” & i).Value; “)”
‘ print joined expression to temp column AL
‘ Worksheets(“owssvr”).Range(“AL” & i).Value = Worksheets(“owssvr”).Range(“AJ” & i).Value; ” months (” & Worksheets(“owssvr”).Range(“AK” & i).Value; “)”
Else ‘ since there are no other conditions, the loop continues to the bottom of the column
End If
Next
End Sub
Hi Maria,
If you want to combine values together as a string you can use the ampersand(&) e.g. Range(“A1”).value= Range(“B1”).Value & Range(“C1”).Value.
Paul
Hi Paul,
I’m just reading your article on IF statements – it is FANTASTICALLY in depth, and helped me realize what was wrong with my IF statement. Thank you for taking the time to write such an in depth article on the topic! Can I pay you back with a toolbar customization file that will give you a 50 button toolbar plus two new ribbons?
Hello Sir Paul,
I’m a newbie in using VBA. I want to create a form with Macro Excel. I’ll set an example, I have a dropdown in a cell with the list of countries and a country has its own corresponding number. For example, Australia, Austria and Belgium is equals to 5 and some countries are equal to 1,2,3 or 4. For example, upon choosing Australia in the dropdown and when I click submit, the corresponding value of Australia which is 5 will appear on the other cell. Badly need your help for our project please. Thank You Sir.
Hi Mr. paul
I have a question about IF:
I have 10 rooms and 10 keys and i choose the key with combobox I will give to my guest.
If i give a key number 4 to my guest for the next guest the key number 4 is not available in my combobox, there are only numbers 1,2,3,..,5,6,7,8,9,10. When my guest give me back the key and make checkout, the key number 4 go back to be available.
Thank you for your help
Hello Mr. Paul!
I have a big question… Is it possible to have multiple statements for only on If? How to do it?
I’m not clear on your question?
Hi, I am Mahbub. I am a new learner of Vba I need vba code to find out duplicate of some specific data from a range. such as from these following values, I need to know 350 and 250 are duplicate or not. pls help me. 250,350,400,425,400,325,350
Hi Paul,
I am a CBA novice, in your example, if you image say in colum E I have combined columns A and D (just using the “&” function), I want to filter once of the columns lets say on “history” and then copy and paste special the value in column E to column A (but only for the filtered rows). Hopefully the example makes sense, but I am struggling to get a macro to work.
Thanks
Hi,
great job 🙂
I found this site by surfing similar problem for words.
I want to change but don´t work for me, maybe you know it better.
Sub ElseDemo2()
‘ Get the data range
Dim rg As Range
Set rg = shMarks.Range(“A1”).CurrentRegion
‘ Clear existing classifications
rg.Columns(5).Offset(1).Cells.ClearContents
Dim i As Long, marks As Long, class As String
‘ Go through the marks columns
For i = 2 To rg.Rows.Count
‘ Get the marks
marks = rg.Cells(i, 3).Value & rg.Cells(i, 4) & rg.Cells(i, 5)
‘ check the marks using if and ElseIf
If marks >= Sonnen König RulesThen
class = “Frankreich”
ElseIf marks >= Pasta Teller Rules Then
class = “Italien”
ElseIf marks >= Brat Wurst Like Then
class = “Deutschland”
ElseIf marks >= Essen ToGo Like Then
class = “USA”
Else
‘ All other marks
class = “Fail”
End If
rg.Cells(i, 5).Value = class
Next i
End Sub
how to recognize values less than 1 in a macro example 1.2345
hi everyone. how can i program this in vba
if a > 1 or c>d>e then
g=2
I have problem with c>d>e because VBA doesnt take d>e into considration!!!
If a > 1 or (c > d and d > e) then
I am struggling with excel vb data entry form for my apartment maintenance charges collected every month from the flat owners. I have a combobox in the userform where the option to select the month is built.. The problem is to see that the monthly maintenance charges collected for a particular month gets posted in the selected month column against the particular flat owner. I am stuck here . Help . thanks in advance
Hi Paul,
I’m very new in VBA. And I have a problem with the Range section. I want to color the tab when there is a value in one of the cells from H12 till H43. If I only mention H12 it works.
I have the following code:
If Range(“H12:H43”).Value “” Then
‘ Get the color yellow
ActiveWorkbook.Sheets(“Rep41”).Tab.ColorIndex = 6
Else
‘ The normal color
ActiveWorkbook.Sheets(“Rep41”).Tab.ColorIndex = -4142
End If
Please help me.