In this screencast, I wanted to show you the difference between iterating through a selection versus a range. I've got a column here for name, I call them fur color, and then zip code. These are just hypothetical data. This is in a file called iterating through selection versus range. What we're gonna do is, I'm going to show you how we can search through. We're going to put in a name here, maybe Emily. We're going to put a color, maybe brown. We're going to set up a subroutine that's going to search through the data here, and it's going to tell us in which row is Emily found, that be one, two, three, and in which row is brown found in the color column, and that would be eight. Now, we can do this two ways. We can use a selection. If we're going to use the selection approach, the user has to select the selection before they run this up. That's a selection. Then they'd run the sub and it would output, Emily is in the third row of the name column. Brown is in the eighth row of the color column. The alternative is to use a range. If we use a range, it will not depend upon where we put the cursor, the active cell or a selection. It's just using this fixed range here. Importantly, for both of these approaches, we're going to automatically detect the size of the data. It's not just going to work for data where we have 10 rows as shown here, but it's going to detect the number of rows. If we make a selection, it's going to automatically detect the size of that selection. The first thing we're going to do is, in cell F4 and in Cell F6, we're going to set up data validation. We want a drop-down menu here, so the user can select all of the names in the name column. To do that, over here, I'm going to use the sort unique combination. I've shown you how to do this a couple of screencasts earlier in the course, and it's going to provide us with the names in there. We didn't really need to use the unique function there because all of these names are unique. Let's do the same thing with the colors. We can do sort unique of our colors. I'm going to go up here to data validation in Cell F4. And this is going to be a list. The source is going to be I four there and I'm going to put a pound sign, a spill operator. It's going to detect the entire column. That will enable me to select from the drop-down menu whatever name I'm looking for. Let's do the same thing here. Data validation. This is going to be a list. The source is going to be J4 with a spill operator, the pound sign. Now we have the drop-down list for the colors. Let's go over here to the Visual Basic Editor. We're going to do two subs, two approaches. The first one is named color finder using a selection, and the second one is name color finder using a range. In both scenarios, we're going to dim the number of rows as integer I, which is going to be an index of iteration in our four loop, I is damned as an integer. We're going to then determine the name row, that's going to be an integer and the color row has an integer. The name row is going to be the row that the name we're looking for is found. The color row is the row that the color we're looking for is found. Let's look at the selection approach here. Again, before the user runs this, they have to make a selection. That's the important thing here, that's our selection. The first thing we're going to do is we're going to count the number of rows of our selection. There's 10 rows in this particular selection, but if we had made this selection, there's only four. Now, the approach here is going to be, we're going to set up a four-loop. We're going to iterate through the first column of our selection looking, for Jacob in this case, Cell F4, by the way, has been named name. F6 has been named color. Let's go ahead back to our selection. We're going to iterate through the name row until the first column matches name, and that'll be the name row. Then we're going to iterate through each row. We're going to look through the second column and we're going to look for Brown. When we find that, this will be row 8 in this scenario, then we're going to store that as color row. Let's go back to Visual Basic. We're going to iterate through all the rows. For I equals one to nr, now on each row, each iteration, we're going to check to see if the selection for that row, first column, if that equals range name. Again, name, range name is Cell F4, that has been named name. We're going to check to see if that's true. We have a one line, one way if then statement here, we're going to store that particular value of I as name row. Similarly, we're going to look through the second column, each row iterating I to the number of rows. We're going to check to see if that matches the color that's in Cell F6. When that's true, when we find that match, we're going to store that particular iteration of I, that row that we're looking at in the variable color row. Finally, once we're done iterating through all the rows, we're going to output this in a message box. The name was found in whatever row and the color was found in the row that it was found. Let's step through this. Again, make sure that you have a selection, select it, and then I'm going to press F8. We count the number of rows, that's 10 in this case. For each of the rows, we're going to see if the selection.cells I throw, right now I is 1, first column, if that equals range name. Our name right now is Jerry. That's going to be found in the fourth row. I can press F8 a couple of times. Actually, the second line here we're checking to see if the second column of the Ith row is equal to the color, that's F6, that's not found until Row 8. I can keep going and once we get to I equals 4, you see that we're storing into the variable name row I. I is 4 right now, and then we keep going and going until I is equal to 8. When I is equal to 8, we have found brown in the color column, in the second column. We store that variable I into the color row variable, and that's 8. Then when we're done, we message box that name was found in Row 4 and color it was found in Row 8. Then we're done. Now let's go through the second approach here, and that's using a range object. Instead of selecting a selection before we run the sub like we did with the first one, we don't have to have a selection. The range is going to be automatically detected. It's always going to start in Cell A4, and it's going to detect this region. It's going to count the number of rows that we have, and it's just going to refer to in terms of, for example, A4, B4, and so on. Instead of using selection.rows.count to count NR, we're just going to use the worksheet function count A of columns A. Here we've got 12 non-blank items in Column A. If I wanted to calculate 10 rows from that, I'm going to subtract 2. We're subtracting 2 because we have a title up here in A1, then we have a column heading here in Cell A3. That's how we can get NR. Now we're going to iterate using a for loop for I equals 1 to NR, and instead of selection like we had up here, we're going to use range. We're going to refer to range. If I'm looking for the name match in the first column, I'm going to iterate from A4 to A13 in this case. In general, you'll see that we can concatenate A with I plus 3. I'm adding 3 because, for example, in this case we're starting in Cell A4, that's the first cell we want to start looking at. To get A4 from I equals 1, I have to add 3. This is the most challenging part of setting up this approach is determining what you have to add to your index of iteration. We're looking through finding where in Column A, we have our name, and when that's true, we're assigning to the name row variable, the current index I. We're storing the current row where we find a match. The next line, if range B, so now we're looking through Column B, starting in B4. Again, we're adding 3 to our index I as we iterate. And when we find the color that's been selected, we're going to assign the current row to this variable color row. Then we keep iterating until we've gone through all the rows. Finally, we can just use the same message box line to output the row that the name and color were found. Let's go ahead and run through this. I'm just going to go up here to the macros and you see that I don't have a selection selected. I'm just going to click on NameColorFinderRange, and we're going to run that, and it says name was found in Row 4 and color was found in Row 8. I can always go back here, and we can change these around and we can rerun the subroutine. Hopefully, this screencast gives you a better idea of the difference between iterating through a selection versus a range. This second approach using ranges is probably what I prefer to do most of the time. Thanks for watching.
