Help: Visual Basic Syntax

  • Thread starter Thread starter Al
  • Start date Start date
A

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?
 
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
 
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
 
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)
 
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
 
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
=======================================
 
Back
Top