Entering Values and Formulas into a Range
In addition to formatting ranges, you will also create macros that put values and formulas into cells and retrieve values from cells. First you should understand how references work in formulas in Excel, and then you can see how to create formulas in a macro.
Most formulas perform arithmetic operations on values retrieved from other cells. Excel formulas use cell references to retrieve values from cells. Imagine, for example, a list of Retail prices and Wholesale costs.
Suppose you want to add a column to the list that calculates the gross margin-the difference between the Retail price and the Wholesale cost-for each item. You would put the label Margin in cell D1 and then enter the first formula into cell D2. The formula subtracts the first Wholesale cost (cell C2) from the first Retail price (cell B2). So you would enter =B2-C2 into cell D2.
For each item in the High group, the gross margin is $2.75. Now you need to copy the formula to the other rows. The formula you typed into cell D2 refers explicitly to cells C2 and B2. When you copy the formula to cell D3, you want the formula to automatically adjust to refer to C3 and B3. Fortunately, when you copy the formulas, Excel adjusts the references because, by default, references are relative to the cell that contains the formula. (The Prices worksheet in the practice file contains these formulas.)
If the reference =C2 is found in cell D2, it really means 'one cell to my left.' When you copy the formula to cell D3, it still means 'one cell to my left,' but now that meaning is represented by the reference =C3.
Sometimes you don't want relative references. Imagine, for example, a worksheet that contains various quantities in column A and prices in row 2.
Suppose you want to add formulas to calculate the revenue for each combination. To calculate the first revenue value (cell B3), you need to multiply the first price (cell B2) by the first quantity (cell A3). When you type =B2*A3 into cell B3, you get the correct answer, $50.
But if you copy that formula to cell B4, you get the ridiculous answer of $1000. That's because the cell references are relative. In this version of the formula, you're not really referring to cells B2 and A3; you're referring to 'one cell above me' and 'one cell to my left.' When you put the formula into cell B4, 'one cell above me' now refers to cell B3, not cell B2.
You want the prices to adjust from column to column, and you want the quantities to adjust from row to row, but you always want the price to be from row 2 and the quantity to be from column A. The solution is to put a dollar sign ($) in front of the 2 in the first price reference (B$2) and in front of the A in the first quantity reference ($A3). The formula that should go into cell B3 is =B$2*$A3. The dollar sign 'anchors' that part of the formula, making it absolute. When you copy the formula to the rest of B3:D7, you get correct answers.
The relative portion of the formula changes with the row or column of the cell that contains the formula. The absolute portion remains fixed.
If you want to modify the formula so that it also takes into account the discount value from cell F2, you must make both the row and the column of the discount reference absolute. The correct formula would be =B$2*$A3*(1 $F$2). (The Revenue worksheet in the practice file contains these formulas.)
R1C1 Reference Style
As a default, Excel displays letters for column headings and numbers for row headings, and the upper left cell in the worksheet is cell A1. Referring to cells by letter and number is called A1 reference style. In A1 reference style, however, cell references do not really say what they mean. For example, the reference =B3 says 'cell B3,' but it means 'two cells to my left.' You don't know what the reference really means until you know which cell contains the reference.
Excel has an alternate reference style that uses numbers for both column and row headings. In this alternate reference style, to refer to a cell you use the letter R plus the row number and C plus the column number. The upper left cell in the worksheet is therefore R1C1, and referring to cells by row and column numbers is called R1C1 reference style. In R1C1 reference style, cell references really do say what they mean.
To turn on R1C1 reference style, choose the Tools menu, click Options, and then click the General tab. Select the R1C1 Reference Style check box, and click OK. (To turn off R1C1 reference style, clear the check box.) The R1C1 Reference Style check box affects only formulas you enter directly into the worksheet. As you will see in the following section, a macro can enter formulas using either reference style, regardless of the setting in the Options dialog box.
In R1C1 reference style, to specify a relative reference on the same row or column as the cell with the formula, you simply use an R or a C, without a number. For example, the reference =RC3 means 'the cell in column 3 of the same row as me,' and the reference =R2C means 'the cell in row 2 of the same column as me.'
To specify a relative reference in a different row or column, you indicate the amount of the difference, in square brackets, after the R or the C. For example, the reference =R5C means 'two columns to my right in row 5,' and the reference =R[-1]C means 'one cell above me.'
The correct formula for calculating the gross margin on the Prices worksheet was =B$2-$C2, but only if the formula was entered into cell B2. In R1C1 reference style, the equivalent formula is =R2C-RC1, and it doesn't matter which cell gets the formula. The formula to calculate the discounted price on the Revenue worksheet was =B$2*$A3*(1 $F$2), at least for cell B2. In R1C1 reference style, the same formula is R2C*RC1*(1 R2C6), again, regardless of the target cell.
Put Values and Formulas into a Range
You can explore the properties for putting values and formulas into a range by creating a simple list of incrementing numbers.
All cells have Formula, FormulaR1C1, and Value properties. The Value property and the Formula property are the same when you're writing to the cell. When you read the value of a cell, the Value property gives you the value, and the Formula property gives you the formula using A1 reference style. The FormulaR1C1 property is the same as the Formula property, except that it uses all references in R1C1 reference style, whether assigning a formula to the cell or reading the formula from a cell.
Use the Address of a Range to Build Formulas
Sometimes you need a macro to create formulas that contain references. For example, suppose you have a range of cells such as the one on the Data worksheet and you want totals at the bottom of the two values columns. If the size of the range can change, you don't know until the macro runs which cells should be included in the SUM formula.
Sometimes the list, with headings, might extend from row 2 to row 4, in which case the formula for the values in the Units column should be =SUM(G3:G4) in cell G5. Another time, the range of cells might extend from row 2 to row 10, in which case the formula should be =SUM(G3:G10), this time in cell G11. Interactively, you would use the AutoSum button to create the formulas, but using the AutoSum button while recording a macro always generates formulas with specific addresses. You can't use the AutoSum button if you need to create a macro that can handle ranges of differing sizes. You need a macro that behaves like a simplified version of the AutoSum button.
Ranges are a powerful tool in Excel. You can select ranges, assign them to variables, add formulas to them, name them, and retrieve their addresses. By manipulating ranges, you can build powerful, dynamic worksheet models.