The Range object has a property called Formula. With this property you can insert a worksheet formula into a cell programmatically (that’s geek-speak for “from VBA”). The Formula property is a String data type (just text), so for simple formulas, it’s easy to create.
In cell A11, let’s insert the formula =SUM(A1:A10):
Range("A11").Formula = "=SUM(A1:A10)"
Pretty easy, huh? Well, there are few things you should know before you consider yourself an expert. First, VBA doesn’t like double quotes inside of strings. It gets confused. To deal with this dilemma, you can use two double quotes (“”) or you can use the Chr function to create double quotes. Take this formula
It counts the number of occurrences of Bill in the range A1:A10. But there are double quotes in this formula, so we need the special handling mentioned above.
Range("A11").Formula = "=COUNTIF(A1:A10,""Bill"")"
Range("A11").Formula = "=COUNTIF(A1:A10," & Chr$(34) & "Bill" & Chr$(34) & ")"
Both do the same thing. Thirty-four is the ASCII code for double quotes. The first example is easier to read, in my opinion. Unfortunately, the second example is the way I learned, and old habits are hard to break. I’m working on it though.
While it may be easy to insert a worksheet formula into a cell when the cell references are known, you can run into problems when they must be relative. That is, when you want to insert a formula that sums the previous ten rows regardless of which cell you’re using. For that, the string we pass to Formula gets a little more complicated. I use the Offset property and the Address property to create the formulas.
.Formula = "=SUM(" & _
.Offset(-10, 0).Address(0, 0) & ":" & _
.Offset(-1, 0).Address(0, 0) & ")"
It looks complicated, but it’s not. The Offset property returns a cell a specified number of rows and/or columns away from the reference cell. The Address property returns, as a string, the address of the cell like “A1”. The two arguments for Address determine if dollar signs are used in the reference. Using Offset and Address, it doesn’t matter which cell you’re in, the formula will sum the 10 cells above.
Some people will tell you that you should write the formula in R1C1 notation. For me, Offset/Address is easier to understand. In the time it takes for me to decipher R1C1, I can write a formula like the above 100 times. But for the sake of completeness, it would look like this:
ActiveCell.Formula = "=SUM(R[-10]C:R[-1]C)"
Boy, that was easy, maybe I’ll have to buckle down and learn R1C1. Nah.