Help: Visual Basic Syntax

[Al]

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?

 

[Tim Zych]

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

Hth,
Tim

 

[Kevin Stecyk]

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

 

[RADO]

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]

That works ... Thx Much!

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

Hth,
Tim






.

 

[Al]

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)

 

[Kevin Stecyk]

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

 

[BrianB]

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
=======================================