Help: Visual Basic Syntax
 

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?

Visual Basic Syntax
 

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?

Visual Basic Syntax
 

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?

Visual Basic Syntax
 

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?

Visual Basic Syntax
 

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?



.

Visual Basic Syntax
 

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?



.

Visual Basic Syntax
 

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?



.

Help: Visual Basic Syntax
 

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?