Iterating on several Cells within a Row

[Dick Watson]

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.

 

[Jim Cone]

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"
<[email protected]>
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.

 

[Joel]

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.

 

[Jacob Skaria]

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

 

[Dick Watson]

Are you saying I can't nest For Each loops?

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]

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.

 

[Dick Watson]

That did it. Thanks!

 

[Joel]

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]

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!