Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 116

The real advantage of this type of cell referencing will be apparent when you explore variables and looping (in Chapter 3, “VBA Programming Fundamentals”). In most cases, you don't use actual values for the arguments; rather, you use variables.

To enter a value of 5 in the cell directly below the active cell, you can use the following instruction:

ActiveCell.Cells(2, 1) = 5

Think of the preceding example as though it said this: “Start with the active cell and consider this cell as cell A1. Place 5 in the cell in the second row and the first column.”

The second syntax of the Cells property uses a single argument that can range from 1 to 17,179,869,184. This number is equal to the number of cells in an Excel worksheet. The cells are numbered starting from A1 and continuing right and then down to the next row. The 16,384th cell is XFD1; the 16,385th cell is A2.

The next example enters the value 2 into cell SZ1 (which is the 520th cell in the worksheet) of the active worksheet:

ActiveSheet.Cells(520) = 2

To display the value in the last cell in a worksheet (XFD1048576), use this statement:

MsgBox ActiveSheet.Cells(17179869184)

You can also use this syntax with a Range object. In this case, the cell returned is relative to the Range object referenced. For example, if the Range object is A1:D10 (40 cells), the Cells property can have an argument from 1 to 40 and can return one of the cells in the Range object. In the following example, a value of 2000 is entered in cell A2 because A2 is the 5th cell (counting from the top, to the right, and then down) in the referenced range:

Range("A1:D10").Cells(5) = 2000