Prev: Identifying a Pivot Table
Next: How to find the owner of the active excel workbook
From: Jay on 22 Dec 2009 13:59
In trying to use the ranges created from Union, I can't refer to the cells
properly.

For example, R1=Union(myrange1, myrange2)

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 in the worksheet right below R1.Cells(1,1). It doesn't give me the
address of the second row, fist column cell of R1:

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



From: Jim Thomlinson on 22 Dec 2009 14:41
Your union is not quite right... You missed the set statement

set R1=Union(myrange1, myrange2)

--
HTH...

Jim Thomlinson


"Jay" wrote:

> In trying to use the ranges created from Union, I can't refer to the cells
> properly.
>
> For example, R1=Union(myrange1, myrange2)
>
> 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 in the worksheet right below R1.Cells(1,1). It doesn't give me the
> address of the second row, fist column cell of R1:
>
> Dim I As long
> For I = 1 to R1.Cells.Count
> msgbox R1.Cells(I, 1).Address
> Next I
>
>
>
From: Jay on 22 Dec 2009 23:36
Jim,

Thanks. Sorry I missed that in my post, but I had it in my code. It still
"doesn't work" or I should say I'm not understanding how it works. If I
create a union of cells that are not connected, why can't I use R1.Cells(2)
to access the second cell in the new range? It still refers to the 2nd cell
in the entire column, rather than the second cell in R1. If used R1.Areas to
overcome this as a work-around, but won't that cause problems if two of my
cells in R1 are right next to each other?

"Jim Thomlinson" wrote:

> Your union is not quite right... You missed the set statement
>
> set R1=Union(myrange1, myrange2)
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Jay" wrote:
>
> > In trying to use the ranges created from Union, I can't refer to the cells
> > properly.
> >
> > For example, R1=Union(myrange1, myrange2)
> >
> > 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 in the worksheet right below R1.Cells(1,1). It doesn't give me the
> > address of the second row, fist column cell of R1:
> >
> > Dim I As long
> > For I = 1 to R1.Cells.Count
> > msgbox R1.Cells(I, 1).Address
> > Next I
> >
> >
> >
 | 
Pages: 1
Prev: Identifying a Pivot Table
Next: How to find the owner of the active excel workbook