Page 149 - Excel Progamming Weekend Crash Course

h540629 ch10.qxd 9/2/03 9:34 AM Page 124

124 Saturday Morning

In this code, the “B3” still means “one column to the right and two rows down,” but this
time it is in relation to range1 and not in relation to the worksheet. If range1 refers to
worksheet cell B3 (as from the previous code snippet), the result is that range2 refers to
“one column to the right and two rows down from cell B3” or, in worksheet terms, cell C5.
This is illustrated in Figure 10-2.

One column to the right

A B C
1

2
3 Two rows down
4

5
6

5
6
7

8

Figure 10-2 A relative range location is defined in terms of its position relative to
another range.

When defining a relative range using the Range property, the size of the
original range has no effect on the size or location of the relative range
Note that you create. Only the location of the upper left cell of the original range
matters.
If you want to create a relative range containing more than one cell, use a cell range as
the argument to the Range property. Here’s an example:

Dim range1 As Range, range2 As Range
Set range1 = ActiveSheet.Range(“B3”)
Set range2 = range1.Range(“C4:E5”)
In the last line of code, the “C4” part of the argument specifies the position of the new
range with respect to range1, and the “E5” part of the argument specifies the size of the
new range. The result is that range2 refers to cells D6:F7. Here’s how this works:
1. The original range, range1, refers to cell B3.