Entering values in cells is a fairly frequent requirement in Excel VBA programming and programmatic data entry is often not dissimilar to the manual entering of data. If you want to enter a given piece of data into a cell, you simply use a statement like the following.

rngHeader.value = "Variance"

In this example, "rngHeader" would be the name of a variable containing a reference to a worksheet cell. But what happens when we want to enter a formula into a cell? Well, in one sense, we could say that the process is no different to entering an ordinary value into a cell. Thus, for example, if we wanted to create a formula that totalled a series of cells in column "A" of the worksheet, we could just use a statement along the following lines.

rngTotal.value = "=SUM(A2:A96)"

This statement, though legitimate, is fairly limited since, in most scenarios, formulas need to be entered into several adjacent cells and using cell addressing which includes column letters becomes impractical. We need to be able to work with numbers so that we can get the maximum benefit from such techniques as looping and the use of the Offset method of the range object. The solution is not to use the Value property of the Range object when writing formulas to a worksheet but, instead, to use FormulaR1C1.

During manual data entry, if you create a formula containing cell references in Excel and then copy it to other cells, Excel updates the cell references to reflect the new location of the formula. It replaces the original references with references which are in the same relative positions as the original ones. It is able to do this because it stores references relatively. (You can force Excel to display formulas in the way that Excel actually stores them by choosing Excel Options from the office button, clicking on the formulas category then activating the option "R1C1 reference style".)

Moving back to Excel VBA, if we use the FormulaR1C1 property instead of the Value property and assuming that the cell containing the formula is A97, our statement would look like this.

rngTotal.FormulaR1C1 = "=SUM(R[-96]C:R[-1]C)"

The figure between the square brackets specifies the row and column numbers of the cells being referenced relative to the cell containing the formula. Thus "R[-96]C" refers to a cell which is 96 rows above the cell which contains the formula but in the same column (since there is no qualifier after the "C" part of the reference).

Entering values in cells is a fairly frequent requirement in Excel VBA programming and programmatic data entry is often not dissimilar to the manual entering of data. If you want to enter a given piece of data into a cell, you simply use a statement like the following.

rngHeader.value = "Variance"

In this example, "rngHeader" would be the name of a variable containing a reference to a worksheet cell. But what happens when we want to enter a formula into a cell? Well, in one sense, we could say that the process is no different to entering an ordinary value into a cell. Thus, for example, if we wanted to create a formula that totalled a series of cells in column "A" of the worksheet, we could just use a statement along the following lines.

rngTotal.value = "=SUM(A2:A96)"

This statement, though legitimate, is fairly limited since, in most scenarios, formulas need to be entered into several adjacent cells and using cell addressing which includes column letters becomes impractical. We need to be able to work with numbers so that we can get the maximum benefit from such techniques as looping and the use of the Offset method of the range object. The solution is not to use the Value property of the Range object when writing formulas to a worksheet but, instead, to use FormulaR1C1.

During manual data entry, if you create a formula containing cell references in Excel and then copy it to other cells, Excel updates the cell references to reflect the new location of the formula. It replaces the original references with references which are in the same relative positions as the original ones. It is able to do this because it stores references relatively. (You can force Excel to display formulas in the way that Excel actually stores them by choosing Excel Options from the office button, clicking on the formulas category then activating the option "R1C1 reference style".)

Moving back to Excel VBA, if we use the FormulaR1C1 property instead of the Value property and assuming that the cell containing the formula is A97, our statement would look like this.

rngTotal.FormulaR1C1 = "=SUM(R[-96]C:R[-1]C)"

The figure between the square brackets specifies the row and column numbers of the cells being referenced relative to the cell containing the formula. Thus "R[-96]C" refers to a cell which is 96 rows above the cell which contains the formula but in the same column (since there is no qualifier after the "C" part of the reference).

Excel macro course

You can get up to date information on Excel VBA training courses, visit Macresource Computer Training, a UK IT training company offering Excel VBA training courses at their central London training centre.

Excel macro course: asp.net training

This article, the best article ever, kindly provided by UberArticles.com