Add a second subroutine to Module1 as follows:
Sub TotalSales2()
'Author: Jim DeMarco
'Date: 6/24/07
'Purpose: Adds total sales for all regions by looping through cells in a range
Dim sFormula As String
Dim cell As Range
sFormula = "=SUM(R[-5]C:R[-1]C)"
For Each cell In Range("B8:E8")
cell.FormulaR1C1 = sFormula
Next cell
End Sub
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 23
We??™ve added a variable called cell which is of type Range. You??™ll recall that a range in Excel
can be anything from one to multiple cells. We then walk through the range B8:E8 using a
For...Each statement, visiting each cell in the referenced range. This is much more concise,
easier to read, and easier to maintain. Of course, like in the first example it also assumes you
know the addresses of the cells in the range to receive the formula.
Let??™s look at one last example that, while not completely dynamic, will show you a method
whereby you could easily adapt it to determine the locations for your formula.
Add one more subroutine to Module1:
Sub TotalSales3()
'Author: Jim DeMarco
'Date: 6/24/07
'Purpose: Adds total sales for all regions by moving across columms
Dim sFormula As String
Dim i As Integer
sFormula = "=SUM(R[-5]C:R[-1]C)"
For i = 2 To 5
Cells(8, i).Select
ActiveCell.FormulaR1C1 = sFormula
Next i
End Sub
This time we??™re using a counter variable, i, to loop through columns 2 through 5. We
select each cell in turn and apply the formula to it. Using this method, it becomes apparent
that if we can use code to determine our start and end points for the For loop, we can very easily
create a dynamic method of adding our formula to a variable number of columns or rows.
Pages:
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50