The Cells property returns a Range object containing all (no indices used) or one (row and column indices are specified) of the cells in the active worksheet. When returning all of the cells in a worksheet, you should only use the Cells property with the Application and Worksheet objects, as it would be redundant, and thus confusing, to use it with the Range object. For example,
Range("A1:A10").Cells returns cells A1 through A10, thus making the use of the Cells property unnecessary.
The Cells property will fail when using it with the Application object unless the active document is a worksheet.
To return a single cell from a Worksheet object you must specify an index. The index can be a single value beginning with the left uppermost cell in the worksheet (for example, Cells(5) returns cell E1) or the index can contain a reference to the row and column index (recommended) as shown below.
Cells(1, 4).Value=5 Cells(1, "D").Value =5
This is the familiar notation used throughout this book. Both lines of code will enter the value 5 into cell D1 of the active worksheet. You can either use numerical or string values for the column reference. You should note that the column reference comes second in both examples and is separated from the row reference by a comma. I recommend using the second example above, as there is no ambiguity in the cell reference—though on occasion it's convenient to use a numerical reference for the column index.
Now consider some examples using the Cells property of the Range object.
Range("C5:E7").Cells(2, 2).Value = 50 Range("C5:E7").Cells(2, "A").Value = 50
This code may confuse you because they appear to be trying to return two different ranges within the same line of code; however, that is not the case, but you can use these examples to more carefully illustrate how the Cells property works.
Before reading on, guess in what worksheet cell each of these lines places the value 50. If you guessed cells B2 and A2, respectively, you're wrong. Instead, the value 50 is entered in cells D6 and A6, respectively, when using the above lines of code. Why? It's because the Cells property uses references relative to the selected range. Without the reference to the Range object in each statement (Range("C5:E7")), the current range is the entire worksheet, thus Cells(2,2) returns the range B2; however, when the selected range is C5:E7, Cells(2,2) will return the second row from this range (row 6) and the second column (column D). Using a string in the Cells property to index the column forces the selection of that column regardless of the range selected. The row index is still relative; therefore, the second example above returns the range A6.
Was this article helpful?