return a cell address?

I'm trying to loop through column of text for a word (SECTIONS) and
another (GROUPS). Where these are in the column varies although they
appear in that order. The two words frame a range that includes from 4
to 15 cells.


trycell = c.Value
If trycell = "SECTIONS" Then
c.Activate
rngStart = c.Offset(1, 0)


it's part of a for...next loop with a nested if. rngStart is dim'd as a
range,
but the c.Offset( 1,0) defaults to returning the value in the cell. I
need the range from that and the range from the second word to assign
it to a variable. I will be using the values from the selected range to
create an array. I haven't found anything in 'help' about returning a
cell address. Any ideas would be appreciated.

Andy

c.address would give you the cell address
c.value would give you the value of the cell

--


Gary


"green fox" <a.fox@sympatico.ca> wrote in message
news:1132451772.091364.303140@g47g2000cwa.googlegroups.com...
> I'm trying to loop through column of text for a word (SECTIONS) and
> another (GROUPS). Where these are in the column varies although they
> appear in that order. The two words frame a range that includes from 4
> to 15 cells.
>
>
> trycell = c.Value
> If trycell = "SECTIONS" Then
> c.Activate
> rngStart = c.Offset(1, 0)
>
>
> it's part of a for...next loop with a nested if. rngStart is dim'd as a
> range,
> but the c.Offset( 1,0) defaults to returning the value in the cell. I
> need the range from that and the range from the second word to assign
> it to a variable. I will be using the values from the selected range to
> create an array. I haven't found anything in 'help' about returning a
> cell address. Any ideas would be appreciated.
>
> Andy
>

Make sure you:
Dim RngStart as Range

Then you'll get yelled at when you do this:
rngStart = c.Offset(1, 0)

you'll want:

Set rngStart = c.Offset(1, 0)

==
If you didn't declare RngStart, then excel saw that variable as a Variant.

By not using Set, it _knew_ you wanted to use the value (the default property of
that range).





green fox wrote:
>
> I'm trying to loop through column of text for a word (SECTIONS) and
> another (GROUPS). Where these are in the column varies although they
> appear in that order. The two words frame a range that includes from 4
> to 15 cells.
>
> trycell = c.Value
> If trycell = "SECTIONS" Then
> c.Activate
> rngStart = c.Offset(1, 0)
>
> it's part of a for...next loop with a nested if. rngStart is dim'd as a
> range,
> but the c.Offset( 1,0) defaults to returning the value in the cell. I
> need the range from that and the range from the second word to assign
> it to a variable. I will be using the values from the selected range to
> create an array. I haven't found anything in 'help' about returning a
> cell address. Any ideas would be appreciated.
>
> Andy

--

Dave Peterson

I tried using address, but it didn't appear to return anything...It
made sense to me that that was what address refered to, however I was
unsure of what arguments to use after. I'll try again.

Andy

Thanks Dave,

Even with the changes rngStart returns the value when the condition is
met. I've tried using

Set rngStart = c.Offset(1,0).Address

but I get a type mismatch error.

Do you think I would be better off assigning names to the cells, then
using those to delineate the range?

Andy

..Address will return a string.

It really depends on what you want and how you declared rngStart.

dim rngStart as range
set rngstart = c.offset(1,0)
msgbox rngstart.address

or

dim RngStart as string
rngstart = c.offset(1,0).Address

How did you declare that rngStart variable?

green fox wrote:
>
> Thanks Dave,
>
> Even with the changes rngStart returns the value when the condition is
> met. I've tried using
>
> Set rngStart = c.Offset(1,0).Address
>
> but I get a type mismatch error.
>
> Do you think I would be better off assigning names to the cells, then
> using those to delineate the range?
>
> Andy

--

Dave Peterson

It depends on what and where those cells are.

Your code looks like you have to determine where the first cell is by checking
where "Sections" is. If you knew where Sections was always going to be, then I
would think a range name would work ok.

But as long as you don't know exactly where it's gonna be, I don't think the
range name will work.

green fox wrote:
>
> Thanks Dave,
>
> Even with the changes rngStart returns the value when the condition is
> met. I've tried using
>
> Set rngStart = c.Offset(1,0).Address
>
> but I get a type mismatch error.
>
> Do you think I would be better off assigning names to the cells, then
> using those to delineate the range?
>
> Andy

--

Dave Peterson