Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance in a cell range using VBA
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance in a cell range using VBA
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance in a cell range using VBA
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) "jasontferrell" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance in a cell range using VBA
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) On Mon, 27 Jul 2009 13:13:01 -0700, Excel_VBA_Newb wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell advance i.e. tab | Excel Discussion (Misc queries) | |||
How do I advance the range by one row Excell VBA automatically | Excel Discussion (Misc queries) | |||
How do I advance the range by one row Excell VBA automatically | Excel Discussion (Misc queries) | |||
How do I advance the range by one row Excell VBA automatically | Excel Discussion (Misc queries) | |||
Advance Filter critera range | Excel Programming |