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
The Accidental Blogging Millionaires

The Accidental Blogging Millionaires

Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.

Get My Free Ebook


Post a comment