Working with one Column in a Named Range
 

This must be simple...

I want to iterate across all cells in the second column of a named range
("Data_Range"). The following For-Each does not work...

For Each cell In Range("Data_Range").Columns(2)
Debug.Print cell.Address
Next cell

For each iteration, this prints the address of the entire named range, not
the address of each cell in the range. Why is that?

The following works using the single-parameter version of Cells()...
For i = 1 To Range("Data_Range").Columns(2).Cells.Count
Debug.Print Range("Data_Range").Columns(2).Cells(i).Value
Next I


Steve

Working with one Column in a Named Range
 

did you dim cell as Range?

Try:
Dim c as Range
For Each c In Range("Data_Range").Columns(2).Cells
Debug.Print c.Address
Next cell


--
Tim Williams
Palo Alto, CA


"Steve Drenker" wrote in message ...
This must be simple...

I want to iterate across all cells in the second column of a named range
("Data_Range"). The following For-Each does not work...

For Each cell In Range("Data_Range").Columns(2)
Debug.Print cell.Address
Next cell

For each iteration, this prints the address of the entire named range, not
the address of each cell in the range. Why is that?

The following works using the single-parameter version of Cells()...
For i = 1 To Range("Data_Range").Columns(2).Cells.Count
Debug.Print Range("Data_Range").Columns(2).Cells(i).Value
Next I


Steve

Working with one Column in a Named Range
 

Steve,

From XL 2003 help:

Columns returns a Range object that represents the columns in the specified
range.

So it returns the address of the whole range represented by the column, just
as "Debug.Print Columns(2).Address" returns:

$B:$B

Another way to get what you want is:

For Each cell In Intersect(Range("Data_Range"),
Range("Data_Range").Columns(2))
Debug.Print cell.Address
Next cell

hth,

Doug

"Steve Drenker" wrote in message
...
This must be simple...

I want to iterate across all cells in the second column of a named range
("Data_Range"). The following For-Each does not work...

For Each cell In Range("Data_Range").Columns(2)
Debug.Print cell.Address
Next cell

For each iteration, this prints the address of the entire named range, not
the address of each cell in the range. Why is that?

The following works using the single-parameter version of Cells()...
For i = 1 To Range("Data_Range").Columns(2).Cells.Count
Debug.Print Range("Data_Range").Columns(2).Cells(i).Value
Next I


Steve

Working with one Column in a Named Range
 

Sometimes, just being explicit will help:

Dim Cell as Range
For Each Cell In Range("Data_Range").Columns(2).Cells



Steve Drenker wrote:

This must be simple...

I want to iterate across all cells in the second column of a named range
("Data_Range"). The following For-Each does not work...

For Each cell In Range("Data_Range").Columns(2)
Debug.Print cell.Address
Next cell

For each iteration, this prints the address of the entire named range, not
the address of each cell in the range. Why is that?

The following works using the single-parameter version of Cells()...
For i = 1 To Range("Data_Range").Columns(2).Cells.Count
Debug.Print Range("Data_Range").Columns(2).Cells(i).Value
Next I

Steve

--

Dave Peterson

Working with one Column in a Named Range
 

Dave,

That's one of the syntactical VBA patterns I have trouble remembering.
E.g., I try

"For each ws in Thisworkbook" or "For each ctl in UserForm1"

more often than I care to admit - except I guess I just did (admit it that
is).

Have a good weekend!

Doug


"Dave Peterson" wrote in message
...
Sometimes, just being explicit will help:

Dim Cell as Range
For Each Cell In Range("Data_Range").Columns(2).Cells



Steve Drenker wrote:

This must be simple...

I want to iterate across all cells in the second column of a named range
("Data_Range"). The following For-Each does not work...

For Each cell In Range("Data_Range").Columns(2)
Debug.Print cell.Address
Next cell

For each iteration, this prints the address of the entire named range,
not
the address of each cell in the range. Why is that?

The following works using the single-parameter version of Cells()...
For i = 1 To Range("Data_Range").Columns(2).Cells.Count
Debug.Print Range("Data_Range").Columns(2).Cells(i).Value
Next I

Steve

--

Dave Peterson

Working with one Column in a Named Range
 

Thanks, Dave, Tim & Doug for your help. As always, great information. I
solved this by avoiding the problem since I found I needed an index into
multiple columns (the "2" is the index to the second column)...

With Range("Data_Range")
If (.Cells(j, 2).Value < aROI(i)) Then
Do Some Stuff
End If
End With




in article , Dave Peterson at
wrote on 4/14/06 5:54 PM:

Sometimes, just being explicit will help:

Dim Cell as Range
For Each Cell In Range("Data_Range").Columns(2).Cells



Steve Drenker wrote:

This must be simple...

I want to iterate across all cells in the second column of a named range
("Data_Range"). The following For-Each does not work...

For Each cell In Range("Data_Range").Columns(2)
Debug.Print cell.Address
Next cell

For each iteration, this prints the address of the entire named range, not
the address of each cell in the range. Why is that?

The following works using the single-parameter version of Cells()...
For i = 1 To Range("Data_Range").Columns(2).Cells.Count
Debug.Print Range("Data_Range").Columns(2).Cells(i).Value
Next I

Steve