Cycling through one column in a range

[mikelee101]

Hello,
I have a range that spans several rows and columns. I'm trying to
cycle through the first column in the range so that I can, eventually,
compare it to a value. However, I keep getting a "Type Mismatch" error
in the cycling process. Here's what I have

==========================================
Dim rng1 as range, rng as range, cl as range

Set rng1 = ThisWorkbook.Names("namedrange").RefersToRange
Set rng = rng1.Columns(1)

For Each cl In rng
Debug.Print cl.Value +++++++++> this is where I get the type mismatch
Next cl

===========================================

When I add a watch to rng and expand it, the values that I expect to be
there are there. However, when I add a watch to cl, it appears to just
be a mirror of rng. What would I need to do in order for cl to be one
cell in rng, instead of the entire rng?

Right now, rng1 is 3 columns wide and about 400 rows deep. Any advice
on how I can use the above, or if there is another way I can go about
cycling through the first column of the range?

Thanks to all for the help.

Mike

 

[Jim Cone]

Mike,
Set rng = rng1.Columns(1).Cells
Jim Cone
San Francisco, USA


----- Original Message -----
From: <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Wednesday, December 21, 2005 8:50 AM
Subject: Cycling through one column in a range


Hello,
I have a range that spans several rows and columns. I'm trying to
cycle through the first column in the range so that I can, eventually,
compare it to a value. However, I keep getting a "Type Mismatch" error
in the cycling process. Here's what I have
==========================================
Dim rng1 as range, rng as range, cl as range
Set rng1 = ThisWorkbook.Names("namedrange").RefersToRange
Set rng = rng1.Columns(1)
For Each cl In rng
Debug.Print cl.Value +++++++++> this is where I get the type mismatch
Next cl
===========================================
When I add a watch to rng and expand it, the values that I expect to be
there are there. However, when I add a watch to cl, it appears to just
be a mirror of rng. What would I need to do in order for cl to be one
cell in rng, instead of the entire rng?
Right now, rng1 is 3 columns wide and about 400 rows deep. Any advice
on how I can use the above, or if there is another way I can go about
cycling through the first column of the range?
Thanks to all for the help.
Mike