Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru, etc,
etc?

MyRange.Offset(1)
MyRange.Offset(2)
....etc

Hth,
Tim



"Al" wrote in message
...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru, etc,
etc?

Hi Al,

Tried this, and it appears to work. This is one solution.

Sub Test()

Dim MyRange As Range
Dim iCounter As Integer

For iCounter = 1 To 10
Set MyRange = Worksheets("Sheet1").Range("A" & iCounter & ":H" &
iCounter)
MyRange.Value = iCounter
Next iCounter

End Sub

Regards,
Kevin


"Al" wrote in message
...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru, etc,
etc?

Another approach is

For i=1 to 10
For j=1 to 10
Myrange.Cells(i,j)=...
Next j
Next i

the trick is that Cells(1,1) refers to the first cell in the upper left
corner of your range

Yet another approach is:
Dim c as range

for each c in MyRange
c.value=5 (or whatever you want)
next c

It will go through every cell in your range, without need for any indexes.

Best -
RADO


"Al" wrote in message
...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru, etc,
etc?

That works ... Thx Much!


-----Original Message-----
MyRange.Offset(1)
MyRange.Offset(2)
....etc

Hth,
Tim



"Al" wrote in message
...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru,
etc,
etc?



.

Thx Kevin

You were able to point me in the right direction with the
'("A" & iCounter & ":H" & iCounter)' thing. That answered
half of my next post. To follow thru with the rest. What
would the syntax be if I wanted to add an integer value to
the value of iCounter?

sorta like this w/ wrong syntax:
Range("A" & iCounter+43 & ":H" & iCounter+43)


-----Original Message-----
Hi Al,

Tried this, and it appears to work. This is one solution.

Sub Test()

Dim MyRange As Range
Dim iCounter As Integer

For iCounter = 1 To 10
Set MyRange = Worksheets("Sheet1").Range("A" &
iCounter & ":H" &
iCounter)
MyRange.Value = iCounter
Next iCounter

End Sub

Regards,
Kevin


"Al" wrote in message
...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru,
etc,
etc?



.

Al,

Your code looks okay to me.

Range("A" & iCounter+43 & ":H" & iCounter+43)

See my example below

Regards,
Kevin



This works....

Sub Test()

Dim MyRange As Range
Dim iCounter As Integer

For iCounter = 1 To 10
Set MyRange = Worksheets("Sheet1").Range("A" & iCounter + 5 & ":H" &
iCounter + 5)
MyRange.Value = iCounter
Next iCounter

End Sub




"Al" wrote in message
...
Thx Kevin

You were able to point me in the right direction with the
'("A" & iCounter & ":H" & iCounter)' thing. That answered
half of my next post. To follow thru with the rest. What
would the syntax be if I wanted to add an integer value to
the value of iCounter?

sorta like this w/ wrong syntax:
Range("A" & iCounter+43 & ":H" & iCounter+43)


-----Original Message-----
Hi Al,

Tried this, and it appears to work. This is one solution.

Sub Test()

Dim MyRange As Range
Dim iCounter As Integer

For iCounter = 1 To 10
Set MyRange = Worksheets("Sheet1").Range("A" &
iCounter & ":H" &
iCounter)
MyRange.Value = iCounter
Next iCounter

End Sub

Regards,
Kevin


"Al" wrote in message
...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru,
etc,
etc?



.

Something like this .... ?

'-------------------------------------------
Sub TEST()
Dim MyRange As Range
Dim Rangestr As String
For n = 1 To 10
Rangestr = "A" & n & ":H" & n
Set MyRange = Worksheets("Sheet1").Range(Rangestr)
MyRange.Select
Next
End Sub
'--------------------------------------------

Regards
BrianB
=======================================


"Al" wrote in message ...
Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:H1")

Question:
How can I increment MyRange so that when it's in a loop,
it becomes "A2:H2", then "A3:H3" the next time thru, etc,
etc?