Cells Property

You can use the Cells property of the Application, Worksheet, or Range objects to refer to the Range object containing all the cells in a Worksheet object or Range object. The following two lines of code each refer to a Range object that contains all the cells in the active worksheet:

ActiveSheet.Cells Application.Cells

Because the Cells property of the Application object is a member of <globals>, you can also refer to the Range object containing all the cells on the active worksheet as follows:

Cells

You can use the Cells property of a Range object as follows: Range("A1:D10").Cells

However, this code achieves nothing because it simply refers to the original Range object it qualifies.

You can refer to a specific cell relative to the Range object by using the Item property of the Range object and specifying the relative row and column positions. The row parameter is always numeric. The column parameter can be numeric, or you can use the column letters entered as a string. The following are both references to the Range object containing the B2 cell in the active worksheet:

Because the Item property is the default property of the Range object, you can omit it as follows:

The numeric parameters are particularly useful when you want to loop through a series of rows or columns using an incrementing index number. The following example loops through rows 1 to 10 and columns A to E in the active worksheet, placing values in each cell:

Sub

FillCells()

Dim lRow As Long, lColumn As Long

'Using Cells property to refer to range

'Loop through rows

For lRow = 1 To 10

'Loop through columns

For lColumn = 1 To 5

Cells(lRow, lColumn).Value = lRow * lColumn

Next lColumn

Next lRow

End

Sub

This gives the results shown in Figure 4-2.

flsiiigspbjeri.ilsm a b

G

d

E

F '

I

1

1!

2

3

4

5

2

2

4

G

3

10

3

3

6

9

12

15

4

4

S

12

16

20

J

S

5

10

15

20

25

S

S

12

18

24

30

7

7

14

21

23

35

3

S

16

24

32

40

g

9

18

27

36

45

10

10

20

3D

40

50

11

12

13

14

► B FiHCelb

J

■■■■ «i

Figure 4-2

0 0

Post a comment