From the course: Excel: Learning VBA

Repeat a task using a For…Next loop - Microsoft Excel Tutorial

From the course: Excel: Learning VBA

Start my 1-month free trial Buy for my team

Repeat a task using a For…Next loop

“

- [Instructor] 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 and Excel VBA is to create a for next loop. In this movie, I will show you how to do that. My sample file is 03_01_ForNext and you can find it in the chapter three folder of the exercise files collection. This workbook contains a single worksheet, and on it, I have shipping charges for different weights and you can see those here. I won't read them out, but I will note that there are six of them. So we have 1, 2, 3, 4, 5, 6, and this will make a difference when we create the for next loop to move through the charges. With that in mind, I'll press Alt F11 to move to the Visual Basic Editor, and here, I have a sub-routine that has two for next loops. I'll start at the top to let you know what's going on. The first is that I have defined an array called shipping charges and it is currency, and you'll note that the size is listed as five. The reason I say five instead of six is that in Excel VBA, we start counting from zero. So we have the zeroth element, which is the first, then the number one refers to the second element and so on. Also, I have a counter variable that is defined as an integer. I activate worksheet number one, and then do the same for cell C3. And again, we use range to identify one or more cells. Then we get to the for next loops. So the eye counter variable starts at zero, and then on the next line it assigns the value that is offset by the first time zero rows and zero columns. The next time through this loop, the counter would move up to one. So we would be working with array position one, which again is the second value because we start counting from zero, and that value would be offset by one row. So instead of the value in C3, we would have the value in C4. And that continues all the way up to the element marked as number five, which is of course the sixth item in the array. And then the second for next loop moves through again, zero to five, and it just uses a message box to display each of the values in the array. So it starts at position zero and then moves to five. I'll go ahead and press F5 and we'll see how the code runs. So the first is we have a Microsoft Excel dialogue box that indicates the number 10, and that is cell C3. I'll click okay. Then we get 20, which is the second value, then 30, then 40, 45, and 50, and we're done. So those are the basics of using a for next loop. You can also change how you move through the loop. The default is to move one value at a time, so it goes from zero to one to two, all the way up to five. You can change that behavior by defining a step. So for example, if I go down to the four statement for the second for next loop, and then I do step two, then we'll start with the first value, then the third, and then the fifth. So I've made my change. I'll press F5 to run. Same as before, we start with the first value, which is 10, then the third, which is 30, then the fifth, which is 45, and because the next value would be six, which doesn't occur in the array, we're done. You can also step backwards through an array. What I'll do is change the step to minus one, but then we need to go from five to zero. So what should happen if I did this correctly is that we'll start with the last element in the array, identified as number five, and then go back to zero. So I'll press F5 to run, and I get 50, which is the last element. Click okay, and we're moving back through with the elements in reverse because we changed the step number to step minus one. For next loops let you repeat your code a specific number of times with the number of repetitions controlled either by specifying the number of steps in the initial for statement or by using a variable. You can also skip values using the step keyword, which lets you examine a subset of the values stored within a range, or to change the order in which they are displayed.

Contents