incrementing a range through a loop

Posted by Jim Mocker on December 08, 2000 9:47 AM

How do I, in a for next loop, increment a range of cells from row I to J to K to L to M to N

example

Range(I1:K3)

increment range through each row

ending in
Range (N1:P3)

Posted by Ivan Moala on December 08, 2000 11:43 AM

If I understand correctly then this is one way
to do it.

Sub loopRg()
Dim ColStart As Integer
Dim ColEnd As Integer
Dim x As Integer

'Define your range start & end
ColStart = 9: ColEnd = 10

'x = the number of ranges to go through
For x = 0 To 3
Range(Cells(1, ColStart + x), Cells(3, ColEnd + x)).Select
ColStart = ColStart + 1: ColEnd = ColEnd + 1
Next

End Sub

HTH

Ivan

Posted by Celia on December 08, 2000 3:30 PM

Jim
If you mean that you want to move the range to the right by one column at a time, then Ivan's macro could be revised as follows :-

Sub loopRg()
Dim ColStart As Integer
Dim ColEnd As Integer
Dim x As Integer
'Define your range start & end
ColStart = 9: ColEnd = 11
'x = the number of ranges to go through
For x = 0 To 5
Range(Cells(1, ColStart + x), Cells(3, ColEnd + x)).Select
Next
End Sub

Another way of doing it :-

Sub loopRg()
Dim rng As Range
Dim x As Integer
'Define your start range
Set rng = Range("I1:K3")
'x = the number of columns to go through
For x = 1 To 6
rng.Select
Set rng = rng.Offset(0, 1)
Next
End Sub