VBA writes formulas automatically

For the table below, we'll use VBA to automatically enter formulas in C and D two columns and get the results.

To write a formula automatically, you have to use a function that is formular1c1.

In summary, the Chinese syntax for automatically writing formulas is:

A cell that is the reference object. FORMULAR1C1 = "= Formula name (r[row offset]:c[column offset]:r[row offset]:c[column offset])

Below, we first give the above table two kinds of automatic writing formula VBA code, respectively, as follows:

' The first way

For i = 2 to 5

' Total score Formula

Worksheets (1). Cells (i, 3). FORMULAR1C1 = "=sum (rc[-2]:rc[-1])"

' Mean Division formula

Worksheets (1). Cells (i, 4). FORMULAR1C1 = "=average (Rc[-3]:rc[-2])"

Next I

' The second way

For i = 2 to 5

' Total score Formula

Worksheets (1). Range ("C" & i). FORMULAR1C1 = "=sum (rc[-2]:rc[-1])"

' Mean Division formula

Worksheets (1). Range ("D" & i). FORMULAR1C1 = "=average (rc[-2]:rc[-1])"

Next I

Formula description

Worksheets (1). Cells (i, 3). FORMULAR1C1 or Worksheets (1). Range ("C" & i). FORMULAR1C1, which represents the cell of the Reference object. Where I is a variable, if I equals 2, then:

Worksheets (1). Cells (2, 3). FORMULAR1C1 represents the cell in column 3rd of row 2nd of the first worksheet, that is, C2 cell. Of course, Worksheets (1). Range ("C" & 2). FORMULAR1C1, refers to the C2 cell.

In addition, there is an important concept is RC, such as rc[-2]:rc[-1] what does it mean? This is mentioned above, the RC represents the offset, r represents the row, and C represents the column. Exactly how much is offset, you must use the specified cell as the Reference object. The offset, you can use this method to describe, such as:

r[row offset]:c[column offset] in which the rows and columns can be offset, also can not offset, if given a number, it shows that a certain offset, if not given the data, it is not offset, if the given is a negative, it is left or up, if the given is positive, then to the right or downward offset.

For example, the C2 cell is the Reference object (the C2 position is the 2nd row, column 3rd), then, after R[1]:c[-1], the description row moves down one line to the 3rd row, and the column's offset is minus 1, which indicates 1 rows to the left, and the column becomes 2, so, after such an offset, then the cell is B3.

For example, D6 cells, after r:c[3] offsets (we know that the row does not give an offset, the description is unchanged, and the column has an offset of 3, the description to the right offset 3), the result is G6.

Finally, we go back to the formula, see:

Worksheets (1). Cells (i, 3). FORMULAR1C1 = "=sum (rc[-2]:rc[-1])" Assuming I equals 2

So, worksheets (1). Cells (i, 3). FORMULAR1C1 is equivalent to Worksheets (1). Cells (2, 3). FORMULAR1C1, which is the position of the 2nd row, column 3rd of the first worksheet, is the C2 cell, which is the reference object, then the formula for the C2 cell is: "=sum (Rc[-2]:rc[-1])"

"=sum (Rc[-2]:rc[-1])" How to understand this? This involves the RC offset, which is the C2 cell cell as the Reference object offset, we see that R row offset does not give the argument, the row is unchanged, is the 2nd row, and the columns are given an offset,- 2 represents the left offset of two positions, that is, from column C to the left two position, naturally become a column, then, Rc[-2] becomes A2, and-1 for the left offset a position, from the C column into column B, then, Rc[-1] becomes B2.

Therefore, the automatically written formula "=sum (Rc[-2]:rc[-1])" In cell C2 is actually equivalent to =sum (A2:B2), which is exactly the correct formula we need. Automatically write formulas and RC offset, give you analysis here, already enough detail, other similar, according to this method of reasoning can be.