All right, I'll be going over two examples to demonstrate why it is advantageous in certain scenarios to use R1C1 style in VBA. The first example I've got in this file called bakery shopping list. We've got a list here, we might have employees of a bakery that periodically put in the orders and what they need. So here we've got the employees the amounts of flour, sugar, baking powder and so on. And what we want to do is we want to some each of these columns. So down here I want to sum the total cups of flour. So we're going to do that. We're not going to do this in Excel though like I just did instead what we're going to do is we're going to set this up so it's automated using VBA. I've got a list here on the list 1 tab, I've got a second list on the list 2 tab. So we're going to create this subroutine to work on list 1. We're going to create it such that it's going to adapt to any list. You see that list 1 has ten different orders here different lines, but list 2 has a lot more, list 2 has 15. And so we're going to set this up so our supper team is going to adapt, it's going to detect the number of entries and then it's going to do the summing. So it's going to do the summing in the very bottom row each time. Whenever you're summing rows or averaging rows, doing something column wise or row wise to multiple rows or columns you should think R1C1 style in VBA. Okay, so the very first thing we're going to do and I like to do this in a lot of my examples is we're going to detect the number of entries. So somehow we need to count that. So this is 10, right? This is 10 in this scenario, list 2 is 15. So we somehow need to count that. I like to use the count A function. The count A function in Excel will count the number of items in a column that are not empty. So I can do for example, count A of this entire column and that gives us 12. Now we're going to subtract 2 because we need to subtract the shopping list title for that worksheet and date. So this will give us if I count the number of items in column A and subtract 2, then that's going to be what I call the number of rows or NR. So let's go over to visual basic. We have to dim nr as an integer. That's going to be our number of rows nr. Now we're going to use the worksheet function.CountA. Now the way to refer to the entire column A is to use columns A:A. And then we're going to subtract 2. So that's going to detect the number of rows. 10 for list 1, 15 for list 2 back over here and Excel. The next thing we're going to do is in this entire array. This is a 1 x 10 array. It's always going to be 1 x 10 because we're always going to have 10 ingredients here. Now, what I'm going to do is I'm going to start in C4 always, because the format is always going to be the same, the first three rows will always be the same. So we're always going to start in C4, we're going to offset by the number of rows, we have 10 here, so 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. So we're going to do range C4.offset, nr rows zero columns. That's where we're going with this in VBA code that will get us to this cell, this is the cell in column C of our first empty row. And then I'm going to do .resize and we're going to resize (1,10) and that will refer to this entire array. So if that was confusing to you, I'm going to break it down here, we're going to start in range C4. You could also refer to cells, (4,3).offset. So I'm offsetting the number of rows and zero columns. And then I'm going to do resize one row, ten columns. All right, so that right there, that statement is corresponding to this highlighted region here. And just to make sure I'm going to click away and let's just put a .select on here and let's run through this. So we're counting them number of rows, that's 10 and then we're going to select that region and let's just make sure that we're referring to the correct region which we are okay. But instead of selecting we're not going to select that instead we're going to put a R1C1 style formula in there. And in order for us to determine that formula, I like to go back to Excel type in our normal relative referencing formula and then go into R1C1 style and Excel by changing the options and then copy and paste that formula. So let me show you what I mean. Let's go back here and I'm going to type in some or summing all of those items and then I'm dragging this over. So we've got our A1 style formulas. Now I like to go into file options formulas as you've learned and I like to select the R1C1 reference style. And now we can look in here and you see that all of the formulas are exactly the same. So we're doing (R(-10) C:R (-1)C) and let's just go over here and C it's the exact same formula. That's the formula then I like to copy. So I'm going to do copy press enter let's go back before I forget let's put this back to A1 style. I like to use A1 style as much as I can because that's what people are typically used to, on the clipboard I've got that formula in R1C1 style copied. And let's go back here And I'm just going to do Ctrl+V to paste. Importantly this formula has to be in quotations like that. Now this will work on the sheet that we have. But again we want to make this such that it adapts to different size sheets. So if I went to list 2 for example, this is 15, we need to therefore figure out how we can adapt this formula such that we're always taking into account the number of rows 10 here. This is always going to be 10. We're offsetting the number of rows by the number of rows always, so that's correct. But this we have to change instead of -10 every time after the negative I'm going to put a quotation there and we're going to concatenate this with nr instead of the 10. So I'm going to delete the 10 and then we're going to resume. We're going to concatenate back to our square brackets there and I believe that that's what we need to do because the -1 we don't need to change. All right, so that I think is looking good. Let's go ahead and put this side by side. Let's start with list 1 to make sure it works. I'm going to delete what we had earlier. So let's delete this entire array and now let's go in here press F8. We count the number rose, that's 10 in this case and then in one fell swoop we're going to put that formula in R1C1 style into cells C14 to L14 for list 1. And then we can end the stub and you see if we click in any one of these it's a relative formula because our settings in Excel right now are for A1 style. So this is our A1 style and we're adding those items above it for each column. Now let's make sure it works on list 2. So I'm going to go in here and let's just let's just press F5 and it's doing the exact same thing. Again, it's adapting to the size of the data that's in that worksheet. I just wanted to go back to module 1. I forgot to mention that what I was doing there was on module 2, module 1 I provide this for you in case you're interested. But this is how we can do the same thing I just did, but using A1 style. R1C1 style, go back to module 2 here is very simple. It's just basically one line of code at after we demand we count the number of rows. The A1 style is a lot more complicated. It's got a couple extra lines in here. The difficult thing was setting up this line here. This line required a little bit of thought and this is for loop that makes the A1 style a lot more difficult to use than the R1C1 style. I've got one more example in the next screen cast to kind of demonstrate this R1C1 style, thanks for watching.
