CHAPTER 4
Looping and flow control
In this chapter, you will:
Work with
For...NextloopsGet to know
DoloopsBe introduced to the VBA loop:
For EachUse
If...Then...ElseandSelect Casefor flow control
Loops make your life easier. You might have 20 lines of macro code that do something cool one time. Add a line of code above and below, and suddenly your macro fixes a million rows instead of one row. Loops are a fundamental component of any programming language. If you’ve taken any programming classes—even BASIC—you’ve likely encountered a For...Next loop. Fortunately, VBA supports all the usual loops, plus a special loop that is excellent to use with VBA.
This chapter covers the basic loop constructs:
For...NextDo...WhileDo...UntilWhile...WendDo Until...Loop
This chapter also discusses the useful loop construct that is unique to object-oriented languages: For Each...Next.
For...Next loops
For and Next are common loop constructs. Everything between For and the Next is run multiple times. Each time the code runs, a certain counter variable, specified in the For statement, has a different value.
Consider this code:
For i = 1 to 10
Cells(i, i).Value = i
Next i
As this program starts to run, you need to give the counter variable a name. In this example, the name of the variable is i. The first time through the code, the variable i is set to 1. The first time the loop is executed, i is equal to 1, so the cell in row 1, column 1 is set to 1 (see Figure 4-1).
FIGURE 4-1 After the first iteration through the loop, the cell in row 1, column 1 has the value 1.
Note To improve readability, you should always indent lines of code inside of a loop. It is your preference whether you use 1, 2, 3, or 4 spaces for the indent.
Let’s take a close look at what happens as VBA gets to the line that says Next i. Before this line is run, the variable i is equal to 1. During the execution of Next i, VBA must make a decision. VBA adds 1 to the variable i and compares it to the maximum value in the To clause of the For statement. If it is within the limits specified in the To clause, the loop is not finished. In this case, the value of i is incremented to 2. Code execution then moves back to the first line of code after the For statement. Figure 4-2 shows the state of the program before it runs the Next line. Figure 4-3 shows what happens after the Next line is executed.
FIGURE 4-2 Before the Next i statement is run, i is equal to 1. VBA can safely add 1 to i, and it will be less than or equal to the 10 specified in the To clause of the For statement.
FIGURE 4-3 After the Next i statement is run, i is incremented to 2. Code execution continues with the line of code immediately following the For statement, which writes a 2 to cell B2.
The second time through the loop, the value of i is 2. The cell in row 2, column 2 (that is, cell B2) gets the value 2.
As the process continues, the Next i statement advances i up to 3, 4, and so on. On the tenth pass through the loop, the cell in row 10, column 10 is assigned the value 10.
It is interesting to watch what happens to the variable i on the last pass through Next i. Before running the Next i line, the variable contains 10. VBA is now at a decision point. It adds 1 to the variable i. The value stored in i is now equal to 11, which is greater than the limit in the For...Next loop. VBA then moves execution to the next line in the macro after the Next statement (see Figure 4-4). In case you are tempted to use the variable i later in the macro, it is important to realize that it will be incremented beyond the limit specified in the To clause of the For statement.
FIGURE 4-4 After incrementing i to 11, code execution moves to the line after the Next statement.
The common use for such a loop is to walk through all the rows in a data set and decide to perform some action based on some criteria. For example, to mark all the rows with positive service revenue in column F, you could use this loop:
For i = 2 to 10
If Cells(i, 6).Value > 0 Then
Cells(i, 8).Value = “Service Revenue”
Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 4
End If
Next i
This loop checks each item of data from row 2 through row 10. If there is a positive number in column F, column H of that row has a new label, and the cells in columns A:H of the row are colored using the color index 4, which is green. After this macro has been run, the results look as shown in Figure 4-5.
Using variables in the For statement
The previous example is not very useful in that it works only when there are exactly 10 rows of data. It is possible to use a variable to specify the upper and lower limit of the For statement. This code sample identifies FinalRow with data and then loops from row 2 to that row:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 to FinalRow
If Cells(i, 6).Value > 0 Then
Cells(i, 8).Value = "Service Revenue"
Cells(i, 1).Resize(1, 8).Interior.ColorIndex = 4
End If
Next i
FIGURE 4-5 After the loop completes all nine iterations, any rows with positive values in column F are colored green and have the label ServiceRevenue added to column H.
Warning Exercise caution when using variables. What if the imported file today is empty and has only a heading row? In this case, the FinalRow variable is equal to 1. This makes the first statement of the loop essentially, say, For i = 2 to 1. Because the start number is higher than the end number, the loop does not execute at all. The variable i is equal to 2, and code execution jumps to the line after Next.
Variations on the For...Next loop
In a For...Next loop, it is possible to have the loop variable jump up by something other than 1. For example, you might use it to apply greenbar formatting to every other row in a data set. In this case, you want to have the counter variable i examine every other row in the data set. Indicate this by adding the Step clause to the end of the For statement:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 to FinalRow Step 2
Cells(i, 1).Resize(1, 7).Interior.ColorIndex = 35
Next i
While running this code, VBA adds a light green shading to rows 2, 4, 6, and so on (see Figure 4-6).
FIGURE 4-6 The Step clause in the For statement of the loop causes the action to occur on every other row.
The Step clause can be any number. You might want to check every tenth row of a data set to extract a random sample. In this case, you would use Step 10:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NextRow = FinalRow + 5
Cells(NextRow-1, 1).Value = "Random Sample of Above Data"
For i = 2 to FinalRow Step 10
Cells(i, 1).Resize(1, 8).Copy Destination:=Cells(NextRow, 1)
NextRow = NextRow + 1
Next i
You can also have a For...Next loop run backward from high to low. This is particularly useful if you are selectively deleting rows. To do this, reverse the order of the For statement and have the Step clause specify a negative number:
’ Delete all rows where column C is the Internal rep - S54
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = FinalRow to 2 Step -1
If Cells(i, 3).Value = "S54" Then
Rows(i).Delete
End If
Next i
Note There is a faster way to delete the records, which is discussed in the “Replacing a loop with AutoFilter” section of Chapter 11, “Data mining with Advanced Filter.”
Exiting a loop early after a condition is met
Sometimes you don’t need to execute a whole loop. Perhaps you just need to read through a data set until you find one record that meets a certain criteria. In this case, you want to find the first record and then stop the loop. A statement called Exit For does this.
The following sample macro looks for a row in the data set where service revenue in column F is positive and product revenue in column E is 0. If such a row is found, you might indicate a message that the file needs manual processing today and move the cell pointer to that row:
’ Are there any special processing situations in the data?
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
ProblemFound = False
For i = 2 to FinalRow
If Cells(i, 6).Value > 0 Then
If cells(i, 5).Value = 0 Then
Cells(i, 6).Select
ProblemFound = True
Exit For
End If
End If
Next i
If ProblemFound Then
MsgBox “There is a problem at row” & i
Exit Sub
End If
Nesting one loop inside another loop
It is okay to run a loop inside another loop. The following code has the first loop run through all the rows in a record set while the second loop runs through all the columns:
' Loop through each row and column
' Add a checkerboard format
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
For I = 2 To FinalRow
' For even numbered rows, start in column 1
' For odd numbered rows, start in column 2
If I Mod 2 = 1 Then ' Divide I by 2 and keep remainder
StartCol = 1
Else
StartCol = 2
End If
For J = StartCol To FinalCol Step 2
Cells(I, J).Interior.ColorIndex = 35
Next J
Next I
In this code, the outer loop is using the i counter variable to loop through all the rows in the data set. The inner loop is using the j counter variable to loop through all the columns in that row. Because Figure 4-7 has seven data rows, the code runs through the i loop seven times. Each time through the i loop, the code runs through the j loop six or seven times. This means that the line of code that is inside the j loop ends up being executed several times for each pass through the i loop. Figure 4-7 shows the result.
FIGURE 4-7 The result of nesting one loop inside the other; VBA can loop through each row and then each column.
Do loops
There are several variations of the Do loop. The most basic Do loop is useful for doing a bunch of mundane tasks. For example, suppose that someone sends you a list of addresses going down a column, as shown in Figure 4-8.
In this case, you might need to rearrange these addresses into a database with name in column B, street in column C, and city and state in column D. By setting relative recording (see Chapter 1, “Unleashing the power of Excel with VBA”) and using the shortcut Ctrl+A, you can record this bit of useful code:
Sub FixOneRecord()
' Keyboard Shortcut: Ctrl+Shift+A
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-2, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Range("A1:A3").Select
Selection.EntireRow.Delete
ActiveCell.Select
End Sub
FIGURE 4-8 It would be more useful to have these addresses in a database format to use in a mail merge.
This code is designed to copy one single address into database format. The code also navigates the cell pointer to the name of the next address in the list. Each time you press Ctrl+A, one address is reformatted.
Note Do not assume that the preceding code is suitable for a professional application. Remember that you don’t need to select something before acting on it. However, sometimes macros are written just to automate a one-time mundane task.
Without a macro, a lot of manual copying and pasting would be required. However, with the preceding recorded macro, you can simply place the cell pointer on a name in column A and press Ctrl+Shift+A. That one address is copied into three columns, and the cell pointer moves to the start of the next address (see Figure 4-9).
FIGURE 4-9 After the macro is run once, one address is moved into the proper format, and the cell pointer is positioned to run the macro again.
When you use this macro, you are able to process an address every second using the shortcut. However, when you need to process 5,000 addresses, you do not want to keep running the same macro over and over. In this case, you can use a Do...Loop to set up the macro to run continuously. You can have VBA run this code continuously by enclosing the recorded code with Do at the top and Loop at the end. Now you can sit back and watch the code perform this insanely boring task in minutes rather than hours.
Note that this particular Do...Loop will run forever because there is no mechanism to stop it. This works for the task at hand because you can watch the progress on the screen and press Ctrl+Break to stop execution when the program advances past the end of this database.
This code uses a Do loop to fix the addresses:
Sub FixAllRecords()
Do
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-2, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Range("A1:A3").Select
Selection.EntireRow.Delete
ActiveCell.Select
Loop
End Sub
These examples have shown quick-and-dirty loops that are great for when you need to accomplish a task quickly. The Do...Loop provides a number of options that enable you to have the program stop automatically when it accomplishes the end of the task.
The first option is to have a line in the Do...Loop that detects the end of the data set and exits the loop. In the current example, this could be accomplished by using the Exit Do command in an If statement. If the current cell is on a cell that is empty, you can assume that you have reached the end of the data and stopped processing the loop:
Sub LoopUntilDone()
Do
If Selection.Value = "" Then Exit Do
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-2, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Range("A1:A3").Select
Selection.EntireRow.Delete
ActiveCell.Select
Loop
End Sub
Using the While or Until clause in Do loops
There are four variations of using While or Until. These clauses can be added to either the Do statement or the Loop statement. In each case, the While or Until clause includes some test that evaluates to True or False.
With a Do While <test expression>...Loop construct, the loop is never executed if <test expression> is false. If you are reading records from a text file, you cannot assume that the file has one or more records. Instead, you need to test to see whether you are already at the end of file with the EOF function before you enter the loop:
' Read a text file, skipping the Total lines
Open "C:Invoice.txt" For Input As #1
R = 1
Do While Not EOF(1)
Line Input #1, Data
If Not Left (Data, 5) = "TOTAL" Then
' Import this row
r = r + 1
Cells(r, 1).Value = Data
End If
Loop
Close #1
In this example, the Not keyword EOF(1) evaluates to True after there are no more records to be read from Invoice.txt. Some programmers think it is hard to read a program that contains a lot of instances of Not. To avoid the use of Not, use the Do Until <test expression>...Loop construct:
' Read a text file, skipping the Total lines
Open "C:Invoice.txt" For Input As #1
r = 1
Do Until EOF(1)
Line Input #1, Data
If Not Left(Data, 5) = "TOTAL" Then
' Import this row
r = r + 1
Cells(r, 1).Value = Data
End If
Loop
Close #1
In other examples, you might always want the loop to be executed the first time. In these cases, move the While or Until instruction to the end of the loop. This code sample asks the user to enter sales amounts made that day; it continually asks for sales amounts until the user enters a zero:
TotalSales = 0
Do
x = InputBox( _
Prompt:="Enter Amount of Next Invoice. Enter 0 when done.", _
Type:=1)
TotalSales = TotalSales + x
Loop Until x = 0
MsgBox "The total for today is $" & TotalSales
In the following loop, a check amount is entered, and then it looks for open invoices to which the check can be applied. However, it is often the case that a single check is received that covers several invoices. The following program sequentially applies the check to the oldest invoices until 100% of the check has been applied:
' Ask for the amount of check received. Add zero to convert to numeric.
AmtToApply = InputBox("Enter Amount of Check") + 0
' Loop through the list of open invoices.
' Apply the check to the oldest open invoices and Decrement AmtToApply
NextRow = 2
Do While AmtToApply > 0
OpenAmt = Cells(NextRow, 3)
If OpenAmt > AmtToApply Then
' Apply total check to this invoice
Cells(NextRow, 4).Value = AmtToApply
AmtToApply = 0
Else
Cells(NextRow, 4).Value = OpenAmt
AmtToApply = AmtToApply - OpenAmt
End If
NextRow = NextRow + 1
Loop
Because you can construct the Do...Loop with the While or Until qualifiers at the beginning or end, you have a great deal of subtle control over whether the loop is always executed once, even when the condition is true at the beginning.
While...Wend loops
While...Wend loops are included in VBA for backward compatibility. In the VBA help file, Microsoft suggests that the Do...Loop construction is more flexible. However, because you might encounter While...Wend loops in code written by others, this chapter includes a quick example. In this loop, the first line is always While <condition>. The last line of the loop is always Wend. Note that there is no Exit While statement. In general, these loops are okay, but the Do...Loop construct is more robust and flexible. Because the Do loop offers either the While or the Until qualifier, you can use this qualifier at the beginning or the end of the loop, and you can exit a Do loop early:
' Read a text file, adding the amounts
Open "C:Invoice.txt" For Input As #1
TotalSales = 0
While Not EOF(1)
Line Input #1, Data
TotalSales = TotalSales + Data
Wend
MsgBox "Total Sales=" & TotalSales
Close #1
The VBA loop: For Each
Even though the VBA loop is an excellent loop, the macro recorder never records this type of loop. VBA is an object-oriented language. It is common to have a collection of objects in Excel, such as a collection of worksheets in a workbook, cells in a range, pivot tables on a worksheet, or data series on a chart.
This special type of loop is great for looping through all the items in a collection. However, before discussing this loop in detail, you need to understand a special kind of variable called object variables.
Object variables
At this point, you have seen a variable that contains a single value. When you have a variable such as TotalSales = 0, TotalSales is a normal variable and generally contains only a single value. It is also possible to have a more powerful variable called an object variable that holds many values. In other words, any property associated with the object is also associated with the object variable.
Generally, developers do not take the time to declare variables. Many books implore you to use the DIM statement to identify all your variables at the top of the procedure. This enables you to specify that a certain variable must be of a certain type, such as Integer or Double. Although this saves a tiny bit of memory, it requires you to know up front which variables you plan on using. However, developers tend to whip up a new variable on the fly as the need arises. Even so, there are great benefits to declaring object variables. For example, the VBA AutoComplete feature turns on if you declare an object variable at the top of your procedure. The following lines of code declare three object variables—a worksheet, a range, and a pivot table:
Sub Test()
Dim WSD as Worksheet
Dim MyCell as Range
Dim PT as PivotTable
Set WSD = ThisWorkbook.Worksheets("Data")
Set MyCell = WSD.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Set PT = WSD.PivotTables(1)
...
In this code, you can see that more than an equal sign is used to assign object variables. You also need to use the Set statement to assign a specific object to the object variable.
There are many good reasons to use object variables, not the least of which is the fact that it can be a great shorthand notation. It is easier to have many lines of code refer to WSD than to ThisWorkbook.Worksheets("Data"). In addition, as mentioned earlier, the object variable inherits all the properties of the object to which it refers.
The For Each loop employs an object variable rather than a Counter variable. The following code loops through all the cells in column A:
For Each cell in Range("A1").CurrentRegion.Resize(, 1)
If cell.Value = "Total" Then
cell.Resize(1,8).Font.Bold = True
End If
Next cell
This code uses the .CurrentRegion property to define the current region and then uses the .Resize property to limit the selected range to a single column. The object variable is called Cell. Any name could be used for the object variable, but Cell seems more appropriate than something arbitrary like Fred.
The following code sample searches all open workbooks, looking for a workbook in which the first worksheet is called Menu:
For Each wb in Workbooks
If wb.Worksheets(1).Name = "Menu" Then
WBFound = True
WBName = wb.Name
Exit For
End If
Next wb
This code sample deletes all pivot tables on the current sheet:
For Each pt in ActiveSheet.PivotTables
pt.TableRange2.Clear
Next pt
Flow control: Using If...Then...Else and Select Case
Another aspect of programming that will never be recorded by the macro recorder is the concept of flow control. Sometimes you do not want every line of a program to be executed every time you run a macro. VBA offers two excellent choices for flow control: the If...Then...Else construct and the Select Case construct.
Basic flow control: If...Then...Else
The most common device for program flow control is the If statement. For example, suppose you have a list of products, as shown in Figure 4-10. You want to loop through each product in the list and copy it to either a Fruits list or a Vegetables list. Beginning programmers might be tempted to loop through the rows twice—once to look for fruit and a second time to look for vegetables. However, there is no need to loop through twice because you can use an If...Then...Else construct on a single loop to copy each row to the correct place.
FIGURE 4-10 A single loop can look for fruits or vegetables.
Using conditions
Any If statement needs a condition that is being tested. The condition should always evaluate to TRUE or FALSE. Here are some examples of simple and complex conditions:
If Range("A1").Value = "Title" ThenIf Not Range("A1").Value = "Title" ThenIf Range("A1").Value = "Title" And Range("B1").Value = "Fruit" ThenIf Range("A1").Value = "Title" Or Range("B1").Value = "Fruit" Then
Using If...Then...End If
After the If statement, you can include one or more program lines that will be executed only if the condition is met. You should then close the If block with an End If line. Here is a simple example of an If statement:
Sub ColorFruitRedBold()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
If Cells(i, 1).Value = "Fruit" Then
Cells(i, 1).Resize(1, 3).Font.Bold = True
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3
End If
Next i
MsgBox "Fruit is now bold and red"
End Sub
Either/or decisions: If...Then...Else...End If
Sometimes you will want to do one set of statements if a condition is true and another set of statements if the condition is not true. To do this with VBA, the second set of conditions would be coded after the Else statement. There is still only one End If statement associated with this construct. For example, you could use the following code to color the fruit red and the vegetables green:
Sub FruitRedVegGreen()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
If Cells(i, 1).Value = "Fruit" Then
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3
Else
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50
End If
Next i
MsgBox "Fruit is red / Veggies are green"
End Sub
Using If...ElseIf...End If for multiple conditions
Notice that the product list includes one item that is classified as an herb. Three conditions can be used to test items on the list. It is possible to build an If...End If structure with multiple conditions. First, test to see whether the record is a fruit. Next, use an ElseIf to test whether the record is a vegetable. Then test to see whether the record is an herb. Finally, if the record is none of those, highlight the record as an error. Here’s the code that does all this:
Sub MultipleIf()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
If Cells(i, 1).Value = "Fruit" Then
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3
ElseIf Cells(i, 1).Value = "Vegetable" Then
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50
ElseIf Cells(i, 1).Value = "Herbs" Then
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 5
Else
' This must be a record in error
Cells(i, 1).Resize(1, 3).Interior.ColorIndex = 6
End If
Next i
MsgBox "Fruit is red / Veggies are green / Herbs are blue"
End Sub
Using Select Case...End Select for multiple conditions
When you have many different conditions, it becomes unwieldy to use many ElseIf statements. For this reason, VBA offers another construct, known as the Select Case construct. In your running example, always check the value of the class in column A. This value is called the test expression. The basic syntax of this construct starts with the words Select Case followed by the test expression:
Select Case Cells(i, 1).Value
Thinking about this problem in English, you might say, “In cases in which the record is fruit, color the record with red.” VBA uses a shorthand version of this. You write the word Case followed by the literal "Fruit". Any statements that follow Case "Fruit" are executed whenever the test expression is a fruit. After these statements, you have the next Case statement: Case "Vegetables". You continue in this fashion, writing a Case statement followed by the program lines that are executed if that case is true.
After you have listed all the possible conditions you can think of, you can optionally include a Case Else section at the end. The Case Else section includes what the program should do if the test expression matches none of your cases. Below, the macro adds a note in column D if an unexpected value is found in A. Finally, you close the entire construct with the End Select statement.
The following program does the same operation as the previous macro but uses a Select Case statement:
Sub SelectCase()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
Select Case Cells(i, 1).Value
Case "Fruit"
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 3
Case "Vegetable"
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 50
Case "Herbs"
Cells(i, 1).Resize(1, 3).Font.ColorIndex = 5
Case Else
Cells(i, 4).Value = "Unexpected value!"
End Select
Next i
MsgBox "Fruit is red / Veggies are green / Herbs are blue"
End Sub
Complex expressions in Case statements
It is possible to have fairly complex expressions in Case statements. For example, say that you want to perform the same actions for all berry records:
Case "Strawberry", "Blueberry", "Raspberry"
AdCode = 1
If it makes sense to do so, you might code a range of values in the Case statement:
Case 1 to 20
Discount = 0.05
Case 21 to 100
Discount = 0.1
You can include the keyword Is and a comparison operator, such as > or <:
Case Is < 10
Discount = 0
Case Is > 100
Discount = 0.2
Case Else
Discount = 0.10
Nesting If statements
It is not only possible but also common to nest an If statement inside another If statement. In this situation, it is important to use proper indentation. You often will find that you have several End If lines at the end of the construct. With proper indentation, it is easier to tell which End If is associated with a particular If.
The final macro in this chapter contains a lot of logic that handles the following discount rules:
For fruit, quantities less than 5 cases get no discount.
Quantities of fruit from 5 to 20 cases get a 10% discount.
Quantities of fruit greater than 20 cases get a 15% discount.
For herbs, quantities less than 10 cases get no discount.
Quantities of herbs from 10 cases to 15 cases get a 3% discount.
Quantities of herbs greater than 15 cases get a 6% discount.
For vegetables except asparagus, quantities of 5 cases and greater earn a 12% discount.
Asparagus requires 20 cases for a discount of 12%.
None of the discounts applies if the product is on sale this week. The sale price is 25% off the normal price. This week’s sale items are strawberries, lettuce, and tomatoes.
The code to execute this logic follows:
Sub ComplexIf()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
ThisClass = Cells(i, 1).Value
ThisProduct = Cells(i, 2).Value
ThisQty = Cells(i, 3).Value
' First, figure out if the item is on sale
Select Case ThisProduct
Case "Strawberry", "Lettuce", "Tomatoes"
Sale = True
Case Else
Sale = False
End Select
' Figure out the discount
If Sale Then
Discount = 0.25
Elseif ThisClass = "Fruit" Then
Select Case ThisQty
Case Is < 5
Discount = 0
Case 5 To 20
Discount = 0.1
Case Is > 20
Discount = 0.15
End Select
ElseIf ThisClass = "Herbs" Then
Select Case ThisQty
Case Is < 10
Discount = 0
Case 10 To 15
Discount = 0.03
Case Is > 15
Discount = 0.06
End Select
ElseIf ThisClass = "Vegetables" Then
' There is a special condition for asparagus
If ThisProduct = "Asparagus" Then
If ThisQty < 20 Then
Discount = 0
Else
Discount = 0.12
End If
Else
If ThisQty < 5 Then
Discount = 0
Else
Discount = 0.12
End If ' Is the product asparagus or not?
End If ' Is the product on sale?
Cells(i, 4).Value = Discount
If Sale Then
Cells(i, 4).Font.Bold = True
End If
Next i
Range("D1").Value = "Discount"
MsgBox "Discounts have been applied"
End Sub
Next steps
Loops add a tremendous amount of power to your recorded macros. Any time you need to repeat a process over all worksheets or all rows in a worksheet, using a loop is the way to go. Excel VBA supports the traditional programming loops of For...Next and Do...Loop and the object-oriented loop For Each...Next. Chapter 5, “R1C1-style formulas,” discusses the seemingly arcane R1C1 style of formulas and shows why it is important in Excel VBA.