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.