Loops: Looping X Iterations with For X to Y Loops

Quick reference

• 00:05 At this point, I wanna show you a different kind of loop.
• 00:08 This is called a For loop.
• 00:10 Specifically, this is a For x To y loop.
• 00:15 Where do loop is really, really useful is where we don't necessarily
• 00:19 know the lower and upper boundaries of what we're trying to search through.
• 00:23 So we can say, hey, I wanna keep counting until a specific condition is met.
• 00:27 I'll keep testing it.
• 00:28 And at that point,
• 00:28 I'll step out when I reach whatever the condition is that I'm expecting.
• 00:32 If, on the other hand,
• 00:33 we know that we need to iterate through something a certain number of times, or
• 00:37 we can actually work out what that top time is, we can use a For x To y loop.
• 00:43 The structure for the x to y loop is somewhat different than the do loop.
• 00:47 Cuz do just is do a loop.
• 00:49 And then you may throw a While or an Until in there.
• 00:51 With a For loop, what we do is we actually set it up.
• 00:54 We need a variable to begin with to count because this variable is what's gonna be
• 00:58 our counter.
• 00:59 And then we'll say For what our variable is.
• 01:01 And I usually use Long for this because I know they can go over 65,536,
• 01:06 which is the integer.
• 01:07 So Long allows me a nice big number.
• 01:10 Now, say I want to go For lCount = 1 To 3.
• 01:15 So this could be 1 to 3.
• 01:16 It could be 1 to 10 or 1 to 250,000, it doesn't matter.
• 01:19 And what I'm gonna do is I'm gonna Debug.Print the lCount,
• 01:22 the current iteration of the loop, into the Immediate window.
• 01:26 And what you can see here is that as I go through and
• 01:28 step through here, we'll start off and lCount is 0.
• 01:32 And we're saying that we wanna run this while xCount is between 1 and 3, for
• 01:37 each iteration in between.
• 01:38 So what it's gonna do is it's gonna say, all right, I'm gonna start.
• 01:40 You can see in the Locals window that it is giving us the value of 1.
• 01:45 And it says, all right, no problem, we're gonna print that to the Immediate window.
• 01:48 There it is, we'll say Next.
• 01:50 And then it says, okay, what's the next value between 1 and 3?
• 01:53 Well, it's 2, so it's gonna change to 2.
• 01:55 We'll print that out.
• 01:55 What's the next value?
• 01:56 It's gonna be 3.
• 01:58 Once it gets to the end, we can go and step out of it.
• 02:01 And there we go.
• 02:03 So that gives me the 1 to 3 that I'm actually looking for here.
• 02:07 This has some really cool practical uses.
• 02:10 I use For x To y loops all the time when I'm coding.
• 02:15 And here's one instance where you could use something like this is where we
• 02:19 actually want to go through and maybe count off or rename our worksheets.
• 02:23 So, in this case here, I'm going to do a little bit of a listing of the worksheets.
• 02:27 So let me just clear the Immediate window out.
• 02:30 What we're gonna do here is we're gonna, again, provide our variable for
• 02:34 counting in our loop.
• 02:35 And you'll notice that I have a With statement set up for ActiveWorkbook.
• 02:38 And basically, what it's gonna do is it's gonna say,
• 02:41 I'd like to count from 1 to .Worksheets.Count.
• 02:43 So this is ActiveWorkbook .Worksheets.Count.
• 02:46 So we'll figure out what's the total count of the worksheets
• 02:49 that are in this workbook.
• 02:50 And then what we're gonna do is we're gonna Debug.Print the .Worksheets lCount.
• 02:54 So that's ActiveWorkbook .Worksheets lCount
• 02:57 is the current iteration of the loop.
• 02:59 And we'll get the name.
• 03:01 So let's give it a go.
• 03:02 We'll go through and we'll take a look.
• 03:04 So here's ActiveWorkbook.
• 03:06 We're gonna go and count to 1 to Worksheets.Count.
• 03:09 Now, I don't necessarily know what the Worksheets.Count is.
• 03:12 It could be, well, in this case, I do, it's three.
• 03:15 I can see them all here, all three of them if I counted them manually.
• 03:17 But there could be 300 worksheets in this workbook.
• 03:20 So this will actually allow me to pick up what I'm looking for.
• 03:23 And at this point, it'll Debug.Print Sheet1, Sheet2, Sheet3.
• 03:29 And these are gonna be in the order that they're actually showing up in here.
• 03:32 So if they're out of order because they've been renamed differently,
• 03:34 then they would show up in the order of the actual index number.
• 03:38 So this is where we can actually try and figure out to say,
• 03:42 what are the boundaries?
• 03:42 What's the top and what's the bottom?
• 03:44 If we happen to know them, we can hardcode those values in.
• 03:46 If we need to use a variable to actually determine that, we can do that.
• 03:49 And if we wanna query the object model to go and
• 03:52 actually pick up a specific count based on another object or a collection or
• 03:55 a property, we can absolutely do that as well.
• 03:58 There's one more thing useful to know about these as well.
• 04:01 And that is what if we wanted to count in reverse order?
• 04:04 So, in this case here,
• 04:05 what I'm gonna do is I'm gonna show you that if I were to change this around.
• 04:09 And say let's go with Worksheets.Count to 1.
• 04:12 And let me just clear out the Immediate window here.
• 04:17 If I try to run this right now, You'll notice that nothing happens.
• 04:22 It'll do a count from Worksheets.Count.
• 04:25 And it basically stops there because this number is larger than what we
• 04:28 actually have over here.
• 04:30 So if we try and step through this,
• 04:31 you'll notice that it basically just doesn't do anything.
• 04:35 So in order to make this actually work in reverse direction,
• 04:38 what we need to do is we actually need to put in here step -1.
• 04:42 And what that will do is it'll actually tell us to start counting backwards
• 04:46 instead.
• 04:47 And what you'll see now is that it says, what's lCount?
• 04:50 It says, hey, it's 3.
• 04:52 Okay, no problem.
• 04:53 So we'll go Sheet3, 2, and we'll go 1 backwards.
• 04:58 So slightly different than we had the first time.
• 05:01 But that's because we've actually gone from a higher number to a lower number.
• 05:05 But we've included this step -1
• 05:07 in order to reverse the direction of the counting on the number line.

Lesson notes are only available for subscribers.