Now we have our starting row, R[??“5], five rows above our formula??™s
cell location. Since we??™re working in the same column as our formula, the column reference
will be C. This gives us the starting cell in our formula range of R[??“5]C. Use the same
technique to determine the last cell location (I??™ve used the cell above the formula even though
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 22
it does not contain any data; this is how Excel??™s AutoSum command works). Our finished
range reference is R[??“5]C:R[??“1]C.
Add a string variable to hold the formula:
Dim sFormula As String
Once we??™ve done this, we can assign the variable to each cell in the Totals data row individually.
The finished TotalSales code should look like Listing 1-3.
Listing 1-3. Completed TotalSales Macro
Sub TotalSales()
'Author: Jim DeMarco
'Date: 6/24/07
'Purpose: Adds total sales for all regions
Dim sFormula As String
sFormula = "=SUM(R[-5]C:R[-1]C)"
Range("B8").Select
ActiveCell.FormulaR1C1 = sFormula
Range("C8").Select
ActiveCell.FormulaR1C1 = sFormula
Range("D8").Select
ActiveCell.FormulaR1C1 = sFormula
Range("E8").Select
ActiveCell.FormulaR1C1 = sFormula
End Sub
As you can see, we created the formula once, assigned it to the sFormula variable, and
then selected each target cell and inserted the formula. Of course, this is not the most efficient
method we can use to achieve this.
Using Excel??™s Range object, we can walk through the cells in a given range and set the formula.
Pages:
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49