Welcome to a new module. In this module I'm going to talk to you about something we call R1C1 style. It's going to be new to a lot of you. But it's really helpful when we start doing more advanced things in Visual Basic. I'm going to call this screencast When Macro Recording Fails. I'm going to show you an example and how we can bypass it and the importance of using R1C1 style. I have a file here called Gradebook. You don't necessarily have to work along with me. We're going to revisit this later in the module. But we have a bunch of students here, their student ID, five homework assignments. In here we have 10 students, we've got their scores. What I want to do is be able to set up, I want to automate this. So maybe once a week I'm getting a spreadsheet like this, and I want to automate the following. I want to calculate the average for each homework assignment. I'm going to fill this out, drag that over. But I also want the average for each student on those assignments. I can calculate the average using the average formula. I can drag that down. I want to just be able to do that with a click of a button. Now this is not that hard to do, but some of you might be having really advanced calculations on big blocks of data to do. The techniques I'm going to show you using R1C1 style are going to be really valuable for these types of calculations. We've done that, but maybe we have another spreadsheet here that we want to do the same thing too. We would put in those average formulas on the bottom here, and then on the right column over here. Let's go ahead and record a macro. Maybe a macro will help us do this. A lot of people, they'll get a set of data and they'll say, "Hey, I'm just going to record a macro, and it should work on any set of data regardless of the size,' which is not necessarily true. Let's go ahead and record the macro. I'm going to type in what we just did, the average of those cells. I'm going to drag this over. We're going to average that top row. We're going to drag this down, and then we're going to stop recording. We've got our nice recording and let's just make sure it works. Let's delete that data. Let's go up here to our macros and let's do macro 1. We're going to run that, and boom, it works fine. It works fine for this set of data. But if I go to another set of data where we have different number of students and we have eight homework assignments instead of five. Let's go ahead and run this macro. Macro 1. You see that doesn't work. It actually puts the averages here. You see the formulas there and also in this column. It's looking for the same size as the data that we recorded that macro for. We've messed up the data on sheet 2. Luckily I have the original data here, so I'm just going to restore it using a copy and a paste. Clearly the recording, let's go over to Visual Basic that's in Module 3 here is not going to work for data that has a different size, like I showed you. You can, if you want, you can start with our recording. You can go in here and change it. But I'm going to show you towards the end of this module how we can do this using something known as R1C1 style, and we can code it from scratch. By the way, the macro recorder does record a lot of stuff in R1C1 style. You see a couple of formulas here. You may not recognize that style. This is known as the R1C1 style. You'll learn all about that in this module. Just to first forward to the worked solution for this Gradebook file, and you're going to learn how to do this at the end of this module. We can do these calculations in VBA using A1 style. A1 style is your typical and you type in here equals cell A1. Or we can use the R1C1 style that you're going to be learning about in this module. Let me show you that either way, we can set this up and it'll perform what we want. I'm just going to go ahead and run our A1 style macro. On this data, it's going to calculate the averages. We can also do a different size data sheet here, we run the A1 style and it does that. It's adapting to the size of your data. Let's go ahead and clear sheet 1, and now I'm going to run it using the R1C1 style. I'm going to run that. It does the same thing. Sheet 2, also in that R1C1 style, will do the exact same thing. Let's look at the code for these. Now again, they do the exact same thing, but I wanted to show that using this R1C1 style as opposed to the A1 style that you guys are used to using, it's a lot smaller using R1C1 style. Here we have just 1, 2,3, 4, 5 rows of code, and using the A1 style, it's a lot more complex. We have 1,2,3,4,5,6,7,8,9 lines of code. What makes the A1 style a lot more difficult to use in this case, is we have to implement for next loops. Here we actually have two, for next loops. Bottom line is using this R1C1 style, you can make your code in VBA a lot more straightforward, a lot easier to use and to write. You'll be learning all about the R1C1 style in this module.