The Power of the R1C1 Formula in Excel Macro
Hi – Jim Hudson, OakTree.com. If you’ve ever needed to create a formula in Excel Macro and you needed to reference an offset row or an offset column, the method that you’re looking for — excuse me, the property that you’re looking for is called formula R1C1. So, for example, I have my current cell here in B8, but I want to reference row 1, which would be right here, but I want to reference column -1. In other words, I want to go left 1. So, if I say, “ActiveCell.FormulaR1C1=R1C-1”, what it’s going to do is it’s going to give me 2 because it’s going to the current — it’s going to row 1 and it’s going to the current column, but it’s counting over 1.
Another way you can use this is you can use this to dynamically build formulas using functions. So, here we’re saying, “Hey, I want to sum some numbers in the column immediately to the left.” So, if we run this, then we can see that it gives us 5 because it goes to row 1 through row 2, but it offsets to the left because we’ve decremented here with -1, offsets to the left the column that we’re on and we get our total of 5. Jim Hudson, oaktreeit.com.