Loop with variable
 

I'm trying to use i as variable to loop through three ranges, with no luck..
what am I doing wrong?

Dim TRange1, TRange2, TRange3 As Range

Set TRange1 = Worksheets("Test").Range("B8:H17")
Set TRange2 = Worksheets("Test").Range("B20:H29")
Set TRange3 = Worksheets("Test").Range("B32:H41")
i = 1
For i = 1 To 5
For Each myCell In TRange(i).Cells
--do sth here
Next
i=i+1
next i

knut

Loop with variable
 

One way:

Option Explicit
Sub testme()

Dim TRange(1 To 3) As Range
Dim i As Long
Dim myCell As Range

Set TRange(1) = Worksheets("Test").Range("B8:H17")
Set TRange(2) = Worksheets("Test").Range("B20:H29")
Set TRange(3) = Worksheets("Test").Range("B32:H41")

For i = LBound(TRange) To UBound(TRange)
For Each myCell In TRange(i).Cells
MsgBox myCell.Address(0, 0)
Next myCell
Next i

End Sub

Just some notes:
This line:
Dim TRange1, TRange2, TRange3 As Range
actually only declared Trange3 as a range. TRange1 and TRange2 were declared as
Variants.

You could have used:
dim TRange1 as range, Trange2 as range, Trange3 as range

And when you're looping through an array, it's sometimes useful to use the
lbound() and ubound() to go from the first to last. Then when you change that
array, you'll have fewer spots in your code to change.

And the "For i/next i" will take care of all the incrementing. You don't need
to do that yourself.



Knut wrote:

I'm trying to use i as variable to loop through three ranges, with no luck..
what am I doing wrong?

Dim TRange1, TRange2, TRange3 As Range

Set TRange1 = Worksheets("Test").Range("B8:H17")
Set TRange2 = Worksheets("Test").Range("B20:H29")
Set TRange3 = Worksheets("Test").Range("B32:H41")
i = 1
For i = 1 To 5
For Each myCell In TRange(i).Cells
--do sth here
Next
i=i+1
next i

knut

--

Dave Peterson

Loop with variable
 

Thanks, got it now.

knut
"Dave Peterson" skrev i melding
...
One way:

Option Explicit
Sub testme()

Dim TRange(1 To 3) As Range
Dim i As Long
Dim myCell As Range

Set TRange(1) = Worksheets("Test").Range("B8:H17")
Set TRange(2) = Worksheets("Test").Range("B20:H29")
Set TRange(3) = Worksheets("Test").Range("B32:H41")

For i = LBound(TRange) To UBound(TRange)
For Each myCell In TRange(i).Cells
MsgBox myCell.Address(0, 0)
Next myCell
Next i

End Sub

Just some notes:
This line:
Dim TRange1, TRange2, TRange3 As Range
actually only declared Trange3 as a range. TRange1 and TRange2 were
declared as
Variants.

You could have used:
dim TRange1 as range, Trange2 as range, Trange3 as range

And when you're looping through an array, it's sometimes useful to use the
lbound() and ubound() to go from the first to last. Then when you change
that
array, you'll have fewer spots in your code to change.

And the "For i/next i" will take care of all the incrementing. You don't
need
to do that yourself.



Knut wrote:

I'm trying to use i as variable to loop through three ranges, with no
luck..
what am I doing wrong?

Dim TRange1, TRange2, TRange3 As Range

Set TRange1 = Worksheets("Test").Range("B8:H17")
Set TRange2 = Worksheets("Test").Range("B20:H29")
Set TRange3 = Worksheets("Test").Range("B32:H41")
i = 1
For i = 1 To 5
For Each myCell In TRange(i).Cells
--do sth here
Next
i=i+1
next i

knut

--

Dave Peterson