This screencast is going to build off of the For Each screencast. What I'm going to show here is more of a real world example. I've got a spreadsheet here of the high temperatures and low temperatures in Boulder, Colorado as a function of the year, and then the month, and the day. So we've got all this data here. 2016 was a leap year, so we have 366 rows of data. And what we want to do is just real quickly be able to look through these to see which days exceeded, for example, a temperature of 90 degrees. Or which days had a low of less than a certain value. So, for example, I can run the high temperature program here. And it says, display the days in the year that exceeded what temperature? So if I wanted to have it automatically search through for all the days that exceeded 90 degrees, I could put in 90 there. And then it goes through and it determines all of the days that exceeded 90 degrees, and it also put the date. So it combined the month, day and the year. I've also got this RESET button on here to clear everything. I've got a low temperature, so display the days in the year that were below a certain temperature. So maybe I wanted to look to see which days had a low of less than or equal to 10? And I can do that, and it looks through and it displays the following. So that's where we're going to build in this screencast. So we're going to make this up so that it'll work on any year. We'll see later in the course we're going to automate this, so the user will be able to choose the year, and it's going to open up the file of that year. So because 2016 is a leap year, we don't want to make it always 366 days. So what I'm going to do, is I'm going to have it always start in cell. It doesn't really matter actually, as long as we're starting somewhere in this block of cells. But I'm just going to use A2, or I could use A1, and then I'm going to record a macro. And I've already done this, but you can record a macro for the Ctrl+Shift+8, to select that block of cells. And the code for that is just Selection.CurrentRegion.Select. And then we're going to count the number of rows of our selection, because that'll tell us how many different items we have to search through using the Selection.Rows.Count. I introduce a new variable, nr, so I have to Dim that. We're also going to be iterating through an index, so I'm going to Dim i as an Integer. So now we're ready to go through the iteration. Now you notice this is going to be a selection, and I don't have to start in row 1 because that's just the labels here. So I'm going to start my For loop, For i = 2 to the number of rows. Now inside the For loop, for each row that we're searching through I'm going to check to see if the high temperature exceeds a value that's input by the user. So we first need to input the high temperature from the user. So I've got HT Dimmed as a Double = InputBox ("Display the days in the year that exceeded what temperature?"). So now inside of our For loop, we're going to search through each row to see if column 4 of the selection, row i column 1, 2, 3, 4 is greater than HT. So I've got this If Then, If Selection.Cells(i,4), then the first thing I'm going to do is to find a new variable c. It's going to be a counting number, as an Integer. Once we find a match, I'm going to say c = c + 1, similar to what I did in a previous screencast. And this is a little bit complicated, but I'm saying, so if we find the first match, c will be bumped up from 0 to 1, and that'll be found on the i'th row. I think in the example I did, i was 163. So it'll say Range(G1), that'll be over here, G1 = Selection.Cells, row 163, column 2. So that's the month. It's going to display the month. We're going to concatenate that with a forward slash, then we're going to concatenate that with the day. Concatenate that with the year, which is found in column 1. Similarly in column H, we want to display the actual temperature. So we're going to record those, and write them to columns G and H of the spreadsheet. I've added in the Debug.Assert just so we can make sure that this is working, the writing it to the spreadsheet. So I'm going to go ahead and press Play. Let's do 90 [SOUND]. And it stops at 163, we can debug now. We can bump up c by 1. So we've already found one that exceeded our high temperature that the user input, and then we put over here in column G1, we put the date. In column H1, we enter the high temperature that day. And then I can keep going. So it looks like it's working. I can delete this line, and we're ready to go. There's one more thing I'm going to add. I'm going to add a Reset code, which is just Columns("G:H").Clear, so we're going to clear whatever's in those columns. At the beginning of my HighTemp sub I'm going to put Call Reset, just so we don't forget it's always going to clear those cells. So we're ready to go, and now I can just run this all. Let's do something like 87 this time, and it goes through, it records or writes to the spreadsheet all the days that exceeded 87. So now we're going to make a couple modifications to this. The first modification, we want some labels up here at the top of columns G and H. So I'm going to add in three lines of code. If and only when c = 1, then we're going to say Range("G1") = "Date", and we're going to have temperature. Because of that, we have to shift down everything. So we have to shift all of our writing to the spreadsheet by 1. Another thing I like to do is at the very end, I like to just select cell A1 so the user isn't left with a big selection. And one more thing I like to do, is I like to add this Application.ScreenUpdating = False to the top. So that means once we're into this Sub, it's going to kind of freeze the screen, and the user's not going to be able to see anything going on behind the screens. They're not going to see any cells and selecting going on, and then the result is just displayed at the very end. I'm also going to assign buttons. So I've just gone up here to the Developer tab > Insert > Button, and you can right-click and do Assign Macro. So you can assign it to the subroutine that we just made. And in a minute we're going to make a low temperature modification, so that's why I added those. So I assigned the RESET button to our Reset sub. Now I'm just going to copy and paste, and we're going to make a LowTemp Sub. I'm going to call this LowTemp, we have a LT instead of HT. We have a different prompt, Display the days in the year that were below a certain temperature? We're going to change this HT to an LT. We also need flip this around to a less than. And instead of using column 4, that's the high temperature data, we have to replace the 4 with a 5, so that's the 5th column. We need to also do that down here, because we want to display the 5th column. And I think we're ready to go. So over here on our spreadsheet, I'm going to do the LOW TEMP. And maybe we want to see which days the low temperature was less than 10. So I can do that, and it displays those days. And you'll also notice that when I do this, you don't see the selection being highlighted. So if I put in 10, if you look here you don't see anything that's going on behind the scenes, because I did that Application.ScreenUpdating = False. And all we're left with is the final result, and the active cell has returned to cell A1. One really important thing that I forgot, that I alluded to at the very beginning, was I need to select A2 in this case. If you don't do this and your active cell is not in the original selection, then this entire thing is not going to work, and your code won't work. So this is more of a real world example of how you might use this type of stuff that you've learned in this module to do something quite useful. Thanks for watching.
