Join Curt Frye for an in-depth discussion in this video Repeating a task using a For...Next loop, part of Learning VBA in Excel.
Many of the code segments you write, such as assigning a value to a variable or displaying a message box, will just need to run one time. If you work with arrays or cell ranges though, you might need to repeat a segment several times. The easiest way to repeat code in Excel VBA is to create a For Next Loop. So let's go back to this example here, where I have a set of shipping charges. We have the maximum weight in pounds, and then the cost in dollars here. And I'll show you the code that I'll use to work with them by pressing Alt+F11 to go to the Visual Basic Editor. What this code does is activate cell B3 and then using the For Next Loop, it starts at the value of 0, counts 3 to 5, and draws values from the Worksheet and puts them into the array.
In this subroutine I have two For Next Loops. The first one reads the values from the worksheet and puts them into an array, and the second steps through the array and prints out each of the values. If I run the routine by pressing F5, it displays the first value, which is 10; the next is 20, 30, 40, 45 and 50. Even though my For Next Loop is numbered from zero to five, so it will repeat six times, you don't need to hit every number when you are going through; instead, you can use the Step keyword and assign the increment that you want for the value.
So, for example, if we start out with the number 0, going to 5, but at Step 2, then the first value would be 0, the second would be 2, the third would be 4, and then the maximum is 5, so we won't get to the next value of 6. Let's see how that looks when I run the code. Pressing F5. The first value was 10. That's normal; you always start on the first value. The next is 30. That's the third value, but again, that's numbered as number two within the array, because the array numbering starts at 0. 45, the fifth element and then the code ends.
You can also go backwards using a step with a negative number, but to do that you need to make sure that your Counter variable goes from a high to a low, as opposed from 0 to 5 here. If I went from 5 to 0 with a step of negative 2, and ran the code, then we start at the highest elements; that's number 5. Okay, we go to 40, the fourth element, 20 the second element, and we are done. For Next Loops let you repeat your code a specific number of times, with a number of repetitions controlled either by specifying the number of steps in the initial For statement, or through a variable.
You can also skip values using the Step keyword, which lets you examine a subset of the values stored in a range.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures
Skill Level Advanced
Excel 2016: Power Query (Get & Transform)with Oz du Soleil1h 49m Intermediate
1. Introducing Visual Basic for Applications (VBA)
2. Defining Variables, Constants, and Calculations
3. Adding Logic to Your VBA Code
4. Debugging Your VBA Code
5. Managing Workbook Elements and Data in VBA
6. Adding Advanced Elements to Your Workbook
7. Using Excel Events in Your VBA Code
8. Putting It All Together
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.