Offset Property

The Offset property of the Range object returns a similar object to the Cells property, but is different in two ways. The first difference is that the Offset parameters are zero-based, rather than one-based, as the term offset implies. These examples both refer to the A10 cell:

Range("A10").Cells(1,1) Range("A1Q").Offset(0,0)

The second difference is that the Range object generated by Cells consists of one cell. The Range object referred to by the Offset property of a range has the same number of rows and columns as the original range. The following refers to B2:C3:

Offset is useful when you want to refer to ranges of equal sizes with a changing base point. For example, you might have sales figures for January to December in B1:B12 and want to generate a three-month moving average from March to December in C3:C12. The code to achieve this is:

Sub

MovingAvgerage()

Dim rng As Range

Dim lRow As Long

'Calculate moving average using Offset property

Set rng = Range("B1:B3")

For lRow = 3 To 12

Cells(lRow, "C").Value = WorksheetFunction.Sum(rng) / 3

Set rng = rng.Offset(1, 0)

Next lRow

End

Sub

The result of running the code is shown in Figure 4-4.

® j flKtoeOtactnifslfl

i

A

B

C

D !

E

I

1

Jan

100

2

Feb

123

3 Mar

115

113

4

Apr

140

126

5 'May

120

125

=

6

JuTl

132

131

7

Jul

124

125

8

Aug

120

125

9

Sep

115

120

m

Oct

132

122

il

Nov

143

130

12

Dec

128

134

«I

14

H

' CakDCete L M ovin ^Au ei-age |

Figure 4-4

Was this article helpful?

0 0

Post a comment