I'm going to go over another example where you might be interested in using the R1C1 style. This is a little bit more sophisticated than the example I showed previously. I'm over here in a file called Gradebook. I have two sheets, Sheet 1 and Sheet 2. These represent grade sheets from a class. We have a bunch of students and a bunch of homework assignments. Sheet 1 has 10 students here and five homework assignments, whereas Sheet 2 has 18 students and eight homework assignments. What we're trying to do is set up a single subroutine that's going to adapt to the size of these different grade sheets. The idea behind this is we can give this to multiple teachers at multiple times during the semester. You may have added or removed students or added or moved homework assignments. It's always going to do what you want. Let's go over to Sheet 1. What we're trying to do in this fairly simple example but more complicated than the previous one I showed, is we're trying to calculate in this bottom row, the row below the last student, we're trying to calculate the average on that assignment. The average of Homework 1 is 6.8. We can drag that over, and we want to know all the assignment averages to see maybe if one of them was more difficult or easier than normal. Then we also want to calculate for each student what that student's average was. We want to determine the average at the end here, and we want to be able to put that into that entire column. Similarly, in Sheet 2, we would run a subroutine. It's going to spit out the averages here for the assignments, and the average is here for the various students. That's what we're going to try to do. We're going to implement those average formulas in those different arrays. Now I have two modules here. Module 1 has some code that I'm providing for you, and this is in the A1 style. Take a look at this afterwards, but it uses two for-loops and it's a lot more complex than the way we're going to do it using R1C1 style. I have a sub here, RC style that we're going to fill in. Now the first thing we have to do similar to the previous example, is we're going to have to count the number of students. I'm going to use the count A function. We're going to borrow Excel's count A function. I'm going to do column A, but we have to subtract one for the header. That'll give us the total number of students, which I call NR, the number of rows. Similarly, we're going to calculate the number of assignments. That's going to be count A of row 1. But then we're going to subtract two because we don't need the column A or column B. That'll give us five in this case. I've dim nr as an integer, nc as an integer, nr again is the number of students, nc is the number of assignments. Those are rows and columns. The approach from here, we want to set up this bottom averaging row here, this array. I'm going to start in cell C2. We're going to offset the number of rows. If I have 10 students here, we're going to go 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. We're going to offset nr rows, zero columns, and we're going to re-size. We're going to re-size by one row. This is our output array for the re-size but then it's going to be nc columns, nc is five in this case. This is how we can refer to that bottom row, that array. This is going to be a one row by nc columns wide. The formula for that is going to be the average formula. Similar to how I did this in the previous example let's go over here. I'm going to go File, Options. Let's get this into R1C1 style because now when we look at this, all of those formulas in the array are exactly the same. That's the nice thing about using the R1C1 style. I'm going to copy that. Press "Enter". Before I forget, let's put this back to A1 style. Let's go to the Visual Basic Editor, and now I'm going to paste this here. We have to put that formula in quotations. This will work for Sheet 1, but we have to change this such that it adapts to the size of our particular worksheet. Again, that's going to be this 10. It's not always going to be 10. It's going to be the number of rows or number of students. I'm going to end that quotation. We're going to concatenate that with the number of rows, and then we're going to resume our function there. That should do it for the averages of each of the assignments. Oops, I forgot to put a period in there. Let's put our period in there, and now let's work on the student averages. Now, this is a little more challenging because we're going to use the cell's property. In this case, we have five assignments, but H is actually the eighth column. In general, I'm going to take the number of columns or the number of assignments nc, I'm going to add three. It's going to be Row 2, nc plus three columns. In Sheet 2, let's make sure that that logic holds true. K is the 11th column. We'll have eight assignments, nc will be eight, so 8 plus 3 is 11. In general, we're going to add three to the number of columns, the number of assignments, Row 2. Instead of range, I'm going to use the cell's property Row 2. Again, this is nc plus three. That will get us to the first cell here, either K2 and Sheet 2, or H2. Now what we're going to do is, we don't need to do an offset. I'm going to re-size. This array is going to be nr rows by one column. I'm going to put a resize in here. This is number of rows and one column wide. We're going to put it in a formula here that's going to be in R1C1 style. Let's go back to our worksheet here. Let's go back. I guess I should have kept it in R1C1 style earlier, but let's go back to R1C1 style. You notice that all of these formulas are exactly the same. I click in here, RC negative five square brackets, RC negative one. We're going from the fifth column back to the cell that's in the same row, one column back in all of these. Let's go ahead and copy that formula. Control C, press "Enter". Let's change this back so I don't forget, to A1 style, back to the Visual Basic Editor. Control, paste, control V, and then we put in the quotation marks. Now the thing we have to change is, instead of five columns back, that's going to always be number of C columns back. Then we can concatenate that and finish off that statement. I'm pretty sure this is working fine. Let's go ahead and make sure it works. Let's clear our data on Sheet 1. Then I'm going to go up here to the Macros. Let's run this in RC style. It's doing that, you can always double-check which is a good idea to make sure that it's working properly. Now let's go to Sheet 2 and make sure that it's working for a different size set of data. Different student data, we go to Macros, run this in RC style. Double-check that it's doing everything and it is. Now some of you might want to, instead of having formulas here, you might not want that to be output on your final worksheet. If that's the case, you can always do a copy-paste in VBA. I've done a couple of examples where we do that. Hopefully, this screencast gives you a more advanced idea of how you can use R1C1 style in VBA.
