Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop with variable name? | Excel Discussion (Misc queries) | |||
How to set a variable in Excel and loop | Excel Programming | |||
Creating a formula using the loop variable | Excel Programming | |||
Excel VBA Loop & Variable Reference | Excel Programming | |||
Re-assigning the value of a variable for a for loop from | Excel Programming |