In this screencast, I'm going to show you how to implement the four next loops, and also something new to you guys, which is the four each, which has its place and it's also quite useful. What we're trying to do in this screencast is, I've got a selection, and I want to find the 7s. When I click Find 7s, first of all it's going to have this little title placed into cell C1. And then it's going to spit out the rows that 7 was found in of my original selection. We're also going to implement a Reset on here, which it clears everything in column C. So, that's what we are going to make in this screencast. This is very similar to the, putting it all together example one, that I have the screencast on. So what we're going to do, is we're going to sort of set it up and we're going to find where we have 7s. So I'm going to first Dim i As an Integer, that's going to be an iteration index for our four loop, and nr which is the number of rows. We're going to start with the selection selected, so I can count number rows equal to the Selection.Rows.Count. Next, we're going to iterate through all of those, in this case I have a selection with 13 rows. And we're going to check to see if Selection.Cells i,1 is equal to 7. If that's the case, I'm just going to output it in the message box here for our first iteration of this. So we set it up for i = 1 To nr. We have our next i, If Selection.Cells, (i,1) = 7, then MagBox("I found a 7".). So, let's go through this. So, I'm going to F8 through here, check the 4 which is not a 7, now we're on row 2, and that is a 7. So we're going to message box a 7, and then we keep going and going. So there's another 7, and then there's one more down here. But what we really want to do is be able to record the row, and over here in column C we want to output it, as in the example I showed at the beginning of the screencast. So instead of this message box, we're going to replace that with something. First think I'm going to do is, if we find a match, if we find a 7 then I'm going to set this counting number c, which I also need to dim as an integer. I'm going to set c = c + 1. So that's kind of going to keep a tally of how many sevens we found. Next, instead of putting it in a message box that we found a seven, I'm going to say range C and little c. So little c's going to be our counting number and that's because I want to put the first match in row one. I want to put the second match in row two. So on the first seven that we find, c's going to be equal to one. So I'm going to place into range C1, I'm going to put the row number, and in this case that'll be 2. Then on the second match, c will equal 2, and into range C2 we're going to put i. And i, at the second match, will be row 5. And at the third match, i will be 10 and we're going to put that Into C3, because C at that point on the third match will be equal to 3. So let's go ahead and run this. I step through this. I find my first match in A2, which is when i equals 2 we set C equal to 1. So down in the locals window c is equal to 1. Now, into range C1, because little c is 1, into C1, we place the current row which is i. Then we move up to the next i and we don't find another 7 until row 5. So when we find the second match, c will be bumped up to 2 and now in range C2, we're gnna place the current row that we're in, which is i a nd that's 5. So we put that, and finally we keep going and we find our last match in row 10 and we can finish. I'm going to add in a new sub here which is reset and reset is just going to clear column C. And at the beginning of my fine 7, I'm going to say, Call Reset, Call Reset then will run the reset sub which clears column C. Another thing we just add these three lines here, If c=1 then range C1 equals 7 found in row. So this is going to be in cell C1, 7 found in row. And I'll put a colon there. So that puts a kind of a title in column C. D, but now instead of being C down here, I'm going to have to add one, so we're shifting all the values down by one. So now when I run this, so let's run this, it calls the Reset subroutine, which just clears all the cells in column C. And now we do the same thing that we did earlier, we found a 7, if c is equal to 1, only when c equals 1. Now, I don't start with this because maybe our selection doesn't have any 7s in it, and we don't want that being displayed unless there's some 7s. So if c=1 then we add that title. That's only going to happen the first time, because c is only 1 once. So now we found a 7 in row 2. So we've shifted that down from what we had previously and then we keep going. And we can just resume. And that's how can output where you can find 7s. Now let me show you how we do this using something a little different, and it's new to you guys, it's known as the ForEach. Now for the ForEach, it's a little different, it's more object-oriented so instead of i and number of rows, I'm going to dim two objects. So, I dimmed rng, which is going to be a range as an object, and also itm which is going to be an item in our range as an object. Instead of counting the number of rows, I'm just going to say, Set rng=Selection. So we're going to take the current selection and set it as this range object. Now instead of for i = 1 to nr, we're going to say, For Each, that's where the For Each statement For Each itm, so that's each item in our range. I also need, instead of Next i, it's just Next. Now instead of Selection.Cells, we're just going to say if itm. So if the item in our range is equal to 7, then all of this is the same. We're still going to do the C stuff here. We're going to put the title in Range ("C1"), but here we're going to say Range ("C" & c + 1) = itm.Row. So it's going to give us the row of the item. I should let you guys know, this is pretty important. If your selection doesn't start in A1, so if you shifted this down, it's not going to give you the row number in the selection. That's why I like to use the for next approach that i showed earlier. The for each is going to give you just a row number, the row of the spreadsheet. So it's not going to give you the row of the selection. In other words, if I made this as my selection, the first 7 will be found in row 10, and not row 5. Using the for next approach that I showed earlier, the first 7, if this was my selection, would be found in row 5. So let's go through this, we reset. We clear column C. And then we set range equal to selection. And now we go through and you're doing the same thing as we did earlier using the four each. And we find our sevens and it acts in just the same way as the four next did. The four each is quite popular, I'm a real big fan of just using the four next and iterating with an index number, plenty of people use the four each. We can then, put buttons on here to associate them with the subroutine and the reset subroutine that we created and then we can run it. So I can find 7s and then I can reset it. Thanks for watching.
