Using RC with Mixed References

A mixed reference is one where the row is fixed and the column is allowed to be relative, or where the column is fixed and the row is allowed to be relative. There are many situations where this will be useful.

Imagine you've written a macro to import Invoice.txt into Excel. Using .End(xlUp), you find where the total row should go. As you are entering totals, you know that you want to sum from the row above the formula up to Row 2. The following code would handle that:

Sub MixedReference()

TotalRow = Cells(65536, 1).End(xlUp).Row + 1 Cells(TotalRow, 1).Value = "Total"

Cells(TotalRow, 5).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)" End Sub

In this code, the reference R2C:R[1]C indicates that the formula should add from Row 2 in the same column to the row just above the formula in the current column. Do you see the advantage to R1C1 formulas in this case? A single R1C1 formula with a mixed reference can be used to easily enter a formula to handle an indeterminate number of rows of data (see Figure 6.8).

Figure 6.8

After running the macro, the formulas in Columns E:G of the total row will have a reference to a range that is locked to Row 2, but all other aspects are relative.

E3 Microsoft Excel - Chapler6.xls

&ls Edit yew insert Fftrmat loot Rata fthdow Udp

J - d ...J I J I & J.,- .1 - J A Z - H \M & B

E3 Microsoft Excel - Chapler6.xls

&ls Edit yew insert Fftrmat loot Rata fthdow Udp

J - d ...J I J I & J.,- .1 - J A Z - H \M & B

ail

- X V £ =SUM(GK:G13)

0 0

Post a comment