The Cells property does not iterate cells in a range; rather, it references
the cell at the row and column specified.

--
Rick (MVP - Excel)


"Jay" wrote in message
...
Some more complications...

In trying to use the ranges created from Union, I can't refer to the cells
properly.

If I try the following it works fine:

Dim c as range
For each c in R1.Cells
msgbox c.Address
Next

If I try the following the second iteration when I=2 gives me the address
of
the row right below R1.Cells(1,1). It doesn't give me the address of the
second cell in R1:

Dim I As long
For I = 1 to R1.Cells.Count
msgbox R1.Cells(I, 1).Address
Next I





"Rick Rothstein" wrote:

Give this macro a try...

Sub SelectBfromA()
Dim x As Long, LastRow As Long, R As Range
Const ValueToFind = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To LastRow
If Cells(x, "A").Value = ValueToFind Then
If R Is Nothing Then
Set R = Cells(x, "A")
Else
Set R = Union(R, Cells(x, "A"))
End If
End If
Next
R.Offset(0, 1).Select
End Sub

Just set the ValueToFind constant (in the Const statement) to the value
you
want to find in Column A.

--
Rick (MVP - Excel)


"Jay" wrote in message
...
If I have the following range:

Row/Col A B
1 2 10
2 2 2
3 3 8
4 4 5
5 3 10
6 2 3
7 2 5
8 1 5
9 2 6
10 3 9

Suppose I have a function that is called by MyFunction(B1:B12). Is
there
any way within the MyFunction routine to define a range of the numbers
in
column B for which column A = 2. If I were doing a SumProduct, the
idea
would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I
just
want
a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
would
be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
this
without any kind of For/Next loop.

Thanks for your help.

.