Using The Range Offset Property
If you are serious about learning range programming, this book is a must!!
When looping through a range using the "For Each" syntax, it is very useful to use the Offset property to reference cells that are outside the loop range but are necessary for the programming goal. In the following example, the offset property allows rows outside the range to be copied to targets that are also outside the given range.
Program Code
Option Explicit
Public Sub LoopThroughRange()
' ******************************************************
' Demonstrate Offset Property Used In Range Loop
' ******************************************************
Dim rngMyRange As Range
Dim C As Range
' ******************************************************
' This Will Copy A1:A10 to D1:D10
' B1:B10 to E1:E10
' C1:C10 to F1:F10
' ******************************************************
Set rngMyRange = Range("A1:A10")
For Each C In rngMyRange
C.Offset(0, 3).Value = C.Value
C.Offset(0, 4).Value = C.Offset(0, 1).Value
C.Offset(0, 5).Value = C.Offset(0, 2).Value
Next C
End Sub