Replacing Many A Formulas with a Single RC Formula

After you get used to R1C1-style formulas, they actually seem a lot more intuitive to build. One classic example to illustrate R1C1-style formulas is building a multiplication table. It is easy to build a multiplication table in Excel using a single mixed reference formula.

Building the Table

Enter the numbers 1 through 12 going across B1:M1. Copy and transpose these so the same numbers are going down A2:A13. Now the challenge is to build a single formula that will work in all cells of B2:M13 and that will show the multiplication of the number in Row 1 times the number in Column 1. Using A1-style formulas, you must press the F4 key five times to get the dollar signs in the proper locations. The following is a far simpler formula in R1C1 style:

Sub MultiplicationTable()

1 Build a multiplication table using a single formula Range("B1:M1").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) Range("B1:M1").Font.Bold = True Range("B1:M1").Copy

Range("A2:A13").PasteSpecial Transpose:=True Range("B2:M13").FormulaR1C1 = "=RC1*R1C" Cells.EntireColumn.AutoFit End Sub

The R1C1-style reference =RC1*R1C couldn't be simpler. In English, it is saying, "Take this row's Column 1 and multiply it by Row 1 of this column." It works perfectly to build the multiplication table shown in Figure 6.9.

Figure 6.9

The macro creates a multiplication table.The formula in B2 uses two mixed references: =$A2*B$1.

Microsoft Excel - Chapter6.xls itäj 08 Edit View Insert Fjtmat Iools Esta ¡Sin*™ Heb a, z I. sa a &

0 0

Post a comment