Listing Specifying Individual Cells with the Cells Property

Sub UsingCells() Dim rg As Range Dim nRow As Integer Dim nColumn As Integer Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets(1)

For nColumn = 1 To 10

Set rg = ws.Cells(nRow, nColumn) rg.Value = rg.Address

Next

Next

Set rg = Nothing Set ws = Nothing End Sub

Listing 8.2 also demonstrates two properties of the Range object: Value and Address. All this listing does is loop through a 10 X 10 block of cells and set the value of each cell equal to the address of the cell, as is shown in Figure 8.1. Your first listing that outputs to a worksheet!

As you can see, the Cells property really lends itself well for use within For.. .Next statements or Do.. .Loop statements. By setting up one variable to represent the current row and another to represent the current column, it also makes your code fairly easy to understand.

TIP When using the Cells property to work with a worksheet, you may find it helpful to turn on the R1C1 Reference Style option. In Excel, select Tools ^ Options. On the General tab, check R1C1 Reference Style. The R1C1 reference style uses numbers for columns rather than letters. This is a "sticky" option, which means it comes on whenever the worksheet is opened. Because most people prefer the A1 style, try to remember and turn this option off before you distribute the workbook.

The Worksheet object's Range property is useful in situations in which you need to operate on a group of cells, though you can also use it with a single cell. The syntax of the Range property is as follows:

SomeWorksheetObject.Range( Celll, [Ce112] )

The Celll parameter is required and can be a string that represents the address of the range (as was demonstrated with the Application.Range property) or a string that represents a named range on the worksheet. If the Cell2 parameter is used, Celll could be a Range object.

The Cell2 parameter is optional and can be a range address, a named range, or a Range object. If you use Celll and Cell2 together, you can think of Celll as the cell in the upper-left corner of the range and Cell2 as the cell in the bottom-right corner of the range.

Listing 8.3 demonstrates a few different ways in which you can refer to a range using the Range property of the Worksheet object.

0 0

Post a comment