Advance in a cell range using VBA

[Excel_VBA_Newb]

How can I increment/decrement the address function of a cell range. Can I
move back and forward throughout a cell range?

Dim myCell As String
I.E myRange = Range (A1:A5)

For Each myCell in myRange.Cells
myCell.Adress++ (this is incorrect)
Next myCell

Thanks!

 

[jasontferrell]

The "next" command at the end of your code causes the cell to
increment. You can just use it like this:

For Each myCell in myRange.Cells
'do something with the cell here, like this:
if myCell.value=0 then
myCell.value = "new value"
end if
Next myCell

 

[Excel_VBA_Newb]

Sorry, I wasn't very clear. What if I want to force the advancement to the
next cell (rather than waiting for the for loop to iterate)

 

[Chip Pearson]

It is generally a bad idea to change a loop control variable within
the loop. Odd side-effects can happen. Which, in your case, is beside
the point since modifying the loop control in a For Each loop has no
effect. E.g,

Dim R As Range
For Each R In Range("A1:A10")
Debug.Print R.Row
If R.Row = 5 Then
Set R = R(3, 1)
End If
Next R

display 1, 2, 3, ...., 10 with no interrupts, despite the fact that R
is set 2 rows downward within the loop. However, code like

Dim N As Long
For N = 1 To 10
If N = 5 Then
N = N + 1
End If
Debug.Print N
Next N

will skip element 5: 1, 2, 3, 4, 6, 7, 8, 9, 10.

Debugging and maintaining such code could be troublesome. Your best
bet is not to mess around with the control variable.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)