The Cells property

Rather than use the VBA Range keyword, you can refer to a range via the Cells property.

Notice that I wrote Cells property, not Cells object or even Cells Collection. Although Cells may seem like an object (or a collection), it's really not. Rather, Cells is a property that VBA evaluates; VBA then returns an object (more specifically, a Range object). If this seems strange, don't worry. Even Microsoft appears to be confused about this issue. In some earlier versions of Excel, the Cells property was known as the Cells method. Regardless of what it is, just understand that Cells is a handy way to refer to a range.

The Cells property takes two arguments: row and column. For example, the following expression refers to cell C2 on Sheet2:

Worksheets("Sheet2").Cells(2, 3)

You can also use the Cells property to refer to a multicell range. The following example demonstrates the syntax you use:

This expression refers to a 100-cell range that extends from cell A1 (row 1, column 1) to cell J10 (row 10, column 10).

The following statements both produce the same result; they enter a value of 99 into a 10-by-10 range of cells. More specifically, these statements set the Value property of the Range object:

The advantage of using the Cells method to refer to ranges becomes apparent when you use variables rather than actual numbers as the Cells arguments. And things really start to click when you understand looping, which I cover in Chapter 10.

0 0

Post a comment