The Cells property

Another way to reference a range is to use the Cells property. You can use the Cells property, like the Range property, on Worksheet objects and Range objects. Check the Help system, and you see that the Cells property has three syntaxes:

object.Cells(rowIndex, columnlndex) object.Cells(rowIndex) object.Cells

I'll give you some examples that demonstrate how to use the Cells property. The first example enters the value 9 into cell A1 on Sheet1. In this case, I'm using the first syntax, which accepts the index number of the row (from 1 to 1048576) and the index number of the column (from 1 to 16384):

Here's an example that enters the value 7 into cell D3 (that is, row 3, column 4) in the active worksheet:

ActiveSheet.Cells(3, 4) = 7

You can also use the Cells property on a Range object. When you do so, the Range object returned by the Cells property is relative to the upper-left cell of the referenced Range. Confusing? Probably. An example might help clear this up. The following instruction enters the value 5 into the active cell. Remember, in this case, the active cell is treated as if it were cell A1 in the worksheet:

Note The real advantage of this type of cell referencing will be apparent when I discuss variables and looping (see Chapter 8). In most cases, you don't use actual values for the arguments; rather, you use variables.

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

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 method 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 2007 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 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)

This syntax can also be used 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 4 0 and can return one of the cells in the Range object. In the following example, a value of 2 00 0 is entered into cell A2 because A2 is the fifth cell (counting from the top, to the right, and then down) in the referenced range:

Note In the preceding example, the argument for the Cells property is not limited to values between 1 and 4 0. If the argument exceeds the number of cells in the range, the counting continues as if the range were taller than it actually is. Therefore, a statement like the preceding one could change the value in a cell that's outside of the range A1:D10. The statement that follows, for example, changes the value in cell A11:

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

The third syntax for the Cells property simply returns all cells on the referenced worksheet. Unlike the other two syntaxes, in this one, the return data is not a single cell. This example uses the ClearContents method on the range returned by using the Cells property on the active worksheet.

The result is that the content of every cell on the worksheet is cleared:

ActiveSheet.Cells.ClearContents

+1 0

Responses

Post a comment