Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
I'm trying to selectively walk a subset of cells within an iteration of rows.
My code looks something like this: For Each myRow In myRows.Rows ' is it a line we need to get the column data from? If myRow.Cells(, ColX) = AmagicValue Then ' if it's magic, let's iterate on the cells of this row_ from column ColY to column ColZ For Each myCell In myRow.Range( _ myRow.Cells(, ColY), _ myRow.Cells(, ColZ) _ ).Cells ' do what we need to do with each of the _ subset of cells in our columns in the magic row Next myCell End If Next myRow This all works fine except the myCell objects end up pointing to the wrong row addresses. If myRow.Address = $A$5:$K$5 then myCell.Address = $E$9 for instance. The $E is great. The $9 is off by 4 rows. And so it goes as this error increases as we walk down the rows in the range. I'm sure I'm missing something really obvious, but I'm now in brain-lock and just not seeing what it is. Thanks in advance to anybody who can correct the error of my ways. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
For Each myrow In myRows.Rows
' is it a line we need to get the column data from? If myrow.Cells(, ColX) = AmagicValue Then 'iterate on the cells of the row from column ColY to column ColZ For Each myCell In Range(Cells(myrow, ColY), Cells(myrow, ColZ)).Cells ' do what we need to do Next myCell End If Next ,myrow -- Jim Cone Portland, Oregon USA "Dick Watson" wrote in message ... I'm trying to selectively walk a subset of cells within an iteration of rows. My code looks something like this: For Each myRow In myRows.Rows ' is it a line we need to get the column data from? If myRow.Cells(, ColX) = AmagicValue Then ' if it's magic, let's iterate on the cells of this row_ from column ColY to column ColZ For Each myCell In myRow.Range( _ myRow.Cells(, ColY), _ myRow.Cells(, ColZ) _ ).Cells ' do what we need to do with each of the _ subset of cells in our columns in the magic row Next myCell End If Next myRow This all works fine except the myCell objects end up pointing to the wrong row addresses. If myRow.Address = $A$5:$K$5 then myCell.Address = $E$9 for instance. The $E is great. The $9 is off by 4 rows. And so it goes as this error increases as we walk down the rows in the range. I'm sure I'm missing something really obvious, but I'm now in brain-lock and just not seeing what it is. Thanks in advance to anybody who can correct the error of my ways. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
The index counter of a FOR loop is not defined outside of the FOR loop.
There is no guarantee what that value is going to be. Myrow is not defined once the code get past the NEXT statement. "Dick Watson" wrote: I'm trying to selectively walk a subset of cells within an iteration of rows. My code looks something like this: For Each myRow In myRows.Rows ' is it a line we need to get the column data from? If myRow.Cells(, ColX) = AmagicValue Then ' if it's magic, let's iterate on the cells of this row_ from column ColY to column ColZ For Each myCell In myRow.Range( _ myRow.Cells(, ColY), _ myRow.Cells(, ColZ) _ ).Cells ' do what we need to do with each of the _ subset of cells in our columns in the magic row Next myCell End If Next myRow This all works fine except the myCell objects end up pointing to the wrong row addresses. If myRow.Address = $A$5:$K$5 then myCell.Address = $E$9 for instance. The $E is great. The $9 is off by 4 rows. And so it goes as this error increases as we walk down the rows in the range. I'm sure I'm missing something really obvious, but I'm now in brain-lock and just not seeing what it is. Thanks in advance to anybody who can correct the error of my ways. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
Change the loop within to
For Each mycell In Range(myrow.Cells(, ColY), myrow.Cells(, ColZ)) MsgBox mycell.Address Next If this post helps click Yes --------------- Jacob Skaria "Dick Watson" wrote: I'm trying to selectively walk a subset of cells within an iteration of rows. My code looks something like this: For Each myRow In myRows.Rows ' is it a line we need to get the column data from? If myRow.Cells(, ColX) = AmagicValue Then ' if it's magic, let's iterate on the cells of this row_ from column ColY to column ColZ For Each myCell In myRow.Range( _ myRow.Cells(, ColY), _ myRow.Cells(, ColZ) _ ).Cells ' do what we need to do with each of the _ subset of cells in our columns in the magic row Next myCell End If Next myRow This all works fine except the myCell objects end up pointing to the wrong row addresses. If myRow.Address = $A$5:$K$5 then myCell.Address = $E$9 for instance. The $E is great. The $9 is off by 4 rows. And so it goes as this error increases as we walk down the rows in the range. I'm sure I'm missing something really obvious, but I'm now in brain-lock and just not seeing what it is. Thanks in advance to anybody who can correct the error of my ways. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
Are you saying I can't nest For Each loops?
For Each myRow In myRows.Rows For Each myCell In myRow.Range( _ Next myCell Next myRow "Joel" wrote in message ... The index counter of a FOR loop is not defined outside of the FOR loop. There is no guarantee what that value is going to be. Myrow is not defined once the code get past the NEXT statement. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
Maybe I forgot to mention--myRow is NOT on the current worksheet, so the
unqualified Range() call doesn't work. There's probably an easier way, but that's why I was trying to get the cells that were bounded by the range of cells within the range. "Jim Cone" wrote in message ... For Each myrow In myRows.Rows ' is it a line we need to get the column data from? If myrow.Cells(, ColX) = AmagicValue Then 'iterate on the cells of the row from column ColY to column ColZ For Each myCell In Range(Cells(myrow, ColY), Cells(myrow, ColZ)).Cells ' do what we need to do Next myCell End If Next ,myrow |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
That did it. Thanks!
"Jacob Skaria" wrote in message ... Change the loop within to For Each mycell In Range(myrow.Cells(, ColY), myrow.Cells(, ColZ)) MsgBox mycell.Address Next If this post helps click Yes |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
No. See comments below.
For Each myRow In myRows.Rows For Each myCell In myRow.Range( _ Next myCell Next myRow 'this statement is not valid since myrow is outside the for loop RowCount = myrow.row "Dick Watson" wrote: Are you saying I can't nest For Each loops? For Each myRow In myRows.Rows For Each myCell In myRow.Range( _ Next myCell Next myRow "Joel" wrote in message ... The index counter of a FOR loop is not defined outside of the FOR loop. There is no guarantee what that value is going to be. Myrow is not defined once the code get past the NEXT statement. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterating on several Cells within a Row
What you write is true. I'm not sure how it relates to my code fragment
posted and the issue I was having with it. Let me put a pair of comments in the fragment to better explain: For Each myRow In myRange.Rows ' is it a line we need to get the column data from? If myRow.Cells(, ColX) = AmagicValue Then ' if it's magic, let's iterate on the cells of this row_ from column ColY to column ColZ For Each myCell In myRow.Range( _ myRow.Cells(, ColY), _ myRow.Cells(, ColZ) _ ).Cells ' do what we need to do with each of the _ subset of cells in our columns in the magic row ' problem was that here myCell.Address was pointing _ to a different row than myRow.Address, thus_ hosing everything up Next myCell End If Next myRow ' problem wasn't a reference to myRow or _ myCell here outside of the For Each... In any event, Jakob's posting sorted it out. Thanks for offering to help! "Joel" wrote in message ... No. See comments below. For Each myRow In myRows.Rows For Each myCell In myRow.Range( _ Next myCell Next myRow 'this statement is not valid since myrow is outside the for loop RowCount = myrow.row |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterating Thru Cells | Excel Programming | |||
Iterating thru cells & comparing | Excel Programming | |||
Iterating through cells problem | Excel Programming | |||
Iterating through cells problem | Excel Programming | |||
Iterating through Cells .Net/Excel 2003 | Excel Programming |