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.

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.

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.

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.

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.

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

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

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.

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