Name of range containing ActiveCell?

Is there an easy way to get the name of the range containing the ActiveCell?
I have a macro that loops through all the names in the workbook and sees if
they intersect with the ActiveCell - but isn't there an easier way? I tried
ActiveCell.Range.Name, and getting the row and column references for
Cells(r,c).Range.Name, but nothing worked.

Ed

Ed,

I may be proved wrong, but I wouldn't have thought so. It could be in many
named ranges.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get the name of the range containing the
ActiveCell?
> I have a macro that loops through all the names in the workbook and sees
if
> they intersect with the ActiveCell - but isn't there an easier way? I
tried
> ActiveCell.Range.Name, and getting the row and column references for
> Cells(r,c).Range.Name, but nothing worked.
>
> Ed
>
>

Hadn't thought of that one. You're right, of course, and that could
probably give a Message Box fits! Guess I'll stick with the long way (since
that's what I'm stuck with <g>).
Ed

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eppkYga8EHA.2180@TK2MSFTNGP12.phx.gbl...
> Ed,
>
> I may be proved wrong, but I wouldn't have thought so. It could be in many
> named ranges.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> > Is there an easy way to get the name of the range containing the
> ActiveCell?
> > I have a macro that loops through all the names in the workbook and sees
> if
> > they intersect with the ActiveCell - but isn't there an easier way? I
> tried
> > ActiveCell.Range.Name, and getting the row and column references for
> > Cells(r,c).Range.Name, but nothing worked.
> >
> > Ed
> >
> >
>
>

If the named range is one cell and that corresponds to the activecell, then

activeCell.Name.Name

will work. But it raises an error if the active cell is not in a named
range or the named range includes more than one cell.

Otherwise, you need to loop through the names.

--
Regards,
Tom Ogilvy

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get the name of the range containing the
ActiveCell?
> I have a macro that loops through all the names in the workbook and sees
if
> they intersect with the ActiveCell - but isn't there an easier way? I
tried
> ActiveCell.Range.Name, and getting the row and column references for
> Cells(r,c).Range.Name, but nothing worked.
>
> Ed
>
>

Thanks for the help, Tom. I have no clue as to why it would matter if the
range includes more than one cell - maybe the name then does not belong to
that cell? But it's enough (for now) to know the restrictions I'm operating
under.

Appreciate the boost.
Ed

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OQwguna8EHA.2124@TK2MSFTNGP14.phx.gbl...
> If the named range is one cell and that corresponds to the activecell,
then
>
> activeCell.Name.Name
>
> will work. But it raises an error if the active cell is not in a named
> range or the named range includes more than one cell.
>
> Otherwise, you need to loop through the names.
>
> --
> Regards,
> Tom Ogilvy
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> > Is there an easy way to get the name of the range containing the
> ActiveCell?
> > I have a macro that loops through all the names in the workbook and sees
> if
> > they intersect with the ActiveCell - but isn't there an easier way? I
> tried
> > ActiveCell.Range.Name, and getting the row and column references for
> > Cells(r,c).Range.Name, but nothing worked.
> >
> > Ed
> >
> >
>
>

Ed,

Sub test()
Dim rng As Range, str As String
Set rng = Selection 'or activecell etc
On Error Resume Next
str = rng.Name.Name
If Err.Number Then
str = "Unamed"
Err.Clear
End If
MsgBox rng.Address & vbCr & str
End Sub

Regards,
Peter T

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get the name of the range containing the
ActiveCell?
> I have a macro that loops through all the names in the workbook and sees
if
> they intersect with the ActiveCell - but isn't there an easier way? I
tried
> ActiveCell.Range.Name, and getting the row and column references for
> Cells(r,c).Range.Name, but nothing worked.
>
> Ed
>
>

Peter: Sorry, but it didn't work. I think it has something to do with the
restrictions mentioned by Tom - it doesn't seem to work if the range
contains more than one cell. So I'll use the loop macro I have.

Thanks for the input.
Ed

"Peter T" <peter_t@discussions> wrote in message
news:On$Hyoa8EHA.1452@TK2MSFTNGP11.phx.gbl...
> Ed,
>
> Sub test()
> Dim rng As Range, str As String
> Set rng = Selection 'or activecell etc
> On Error Resume Next
> str = rng.Name.Name
> If Err.Number Then
> str = "Unamed"
> Err.Clear
> End If
> MsgBox rng.Address & vbCr & str
> End Sub
>
> Regards,
> Peter T
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> > Is there an easy way to get the name of the range containing the
> ActiveCell?
> > I have a macro that loops through all the names in the workbook and sees
> if
> > they intersect with the ActiveCell - but isn't there an easier way? I
> tried
> > ActiveCell.Range.Name, and getting the row and column references for
> > Cells(r,c).Range.Name, but nothing worked.
> >
> > Ed
> >
> >
>
>

Ed,

I wonder why it doesn't work for you. For me it works with more than one
cell, a block of cells or even a multiple range, providing the range is
exactly a named range. If a multiple range it would need to be selected in
the same order as defined.

Regards,
Peter T


"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:OgS$gwa8EHA.3376@TK2MSFTNGP12.phx.gbl...
> Peter: Sorry, but it didn't work. I think it has something to do with
the
> restrictions mentioned by Tom - it doesn't seem to work if the range
> contains more than one cell. So I'll use the loop macro I have.
>
> Thanks for the input.
> Ed
>
> "Peter T" <peter_t@discussions> wrote in message
> news:On$Hyoa8EHA.1452@TK2MSFTNGP11.phx.gbl...
> > Ed,
> >
> > Sub test()
> > Dim rng As Range, str As String
> > Set rng = Selection 'or activecell etc
> > On Error Resume Next
> > str = rng.Name.Name
> > If Err.Number Then
> > str = "Unamed"
> > Err.Clear
> > End If
> > MsgBox rng.Address & vbCr & str
> > End Sub
> >
> > Regards,
> > Peter T
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> > > Is there an easy way to get the name of the range containing the
> > ActiveCell?
> > > I have a macro that loops through all the names in the workbook and
sees
> > if
> > > they intersect with the ActiveCell - but isn't there an easier way? I
> > tried
> > > ActiveCell.Range.Name, and getting the row and column references for
> > > Cells(r,c).Range.Name, but nothing worked.
> > >
> > > Ed
> > >
> > >
> >
> >
>
>

I suspect he means that it does not return the range name if the activecell
is located within a multicell named range. In other words, it works, but
not the way he wants it to work.

--
Regards,
Tom Ogilvy




"Peter T" <peter_t@discussions> wrote in message
news:OT8x27a8EHA.3700@tk2msftngp13.phx.gbl...
> Ed,
>
> I wonder why it doesn't work for you. For me it works with more than one
> cell, a block of cells or even a multiple range, providing the range is
> exactly a named range. If a multiple range it would need to be selected in
> the same order as defined.
>
> Regards,
> Peter T
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:OgS$gwa8EHA.3376@TK2MSFTNGP12.phx.gbl...
> > Peter: Sorry, but it didn't work. I think it has something to do with
> the
> > restrictions mentioned by Tom - it doesn't seem to work if the range
> > contains more than one cell. So I'll use the loop macro I have.
> >
> > Thanks for the input.
> > Ed
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:On$Hyoa8EHA.1452@TK2MSFTNGP11.phx.gbl...
> > > Ed,
> > >
> > > Sub test()
> > > Dim rng As Range, str As String
> > > Set rng = Selection 'or activecell etc
> > > On Error Resume Next
> > > str = rng.Name.Name
> > > If Err.Number Then
> > > str = "Unamed"
> > > Err.Clear
> > > End If
> > > MsgBox rng.Address & vbCr & str
> > > End Sub
> > >
> > > Regards,
> > > Peter T
> > >
> > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> > > > Is there an easy way to get the name of the range containing the
> > > ActiveCell?
> > > > I have a macro that loops through all the names in the workbook and
> sees
> > > if
> > > > they intersect with the ActiveCell - but isn't there an easier way?
I
> > > tried
> > > > ActiveCell.Range.Name, and getting the row and column references for
> > > > Cells(r,c).Range.Name, but nothing worked.
> > > >
> > > > Ed
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Tom and Peter: I have a named range which is five rows by 20 columns. I
selected one cell in that range and ran Peter's macro. The message box said
"Unamed". Wouldn't this mean the macro could not see the name of the range
the cell belonged to?

Ed

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23%23lqnOb8EHA.1264@TK2MSFTNGP12.phx.gbl...
> I suspect he means that it does not return the range name if the
activecell
> is located within a multicell named range. In other words, it works, but
> not the way he wants it to work.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:OT8x27a8EHA.3700@tk2msftngp13.phx.gbl...
> > Ed,
> >
> > I wonder why it doesn't work for you. For me it works with more than one
> > cell, a block of cells or even a multiple range, providing the range is
> > exactly a named range. If a multiple range it would need to be selected
in
> > the same order as defined.
> >
> > Regards,
> > Peter T
> >
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:OgS$gwa8EHA.3376@TK2MSFTNGP12.phx.gbl...
> > > Peter: Sorry, but it didn't work. I think it has something to do
with
> > the
> > > restrictions mentioned by Tom - it doesn't seem to work if the range
> > > contains more than one cell. So I'll use the loop macro I have.
> > >
> > > Thanks for the input.
> > > Ed
> > >
> > > "Peter T" <peter_t@discussions> wrote in message
> > > news:On$Hyoa8EHA.1452@TK2MSFTNGP11.phx.gbl...
> > > > Ed,
> > > >
> > > > Sub test()
> > > > Dim rng As Range, str As String
> > > > Set rng = Selection 'or activecell etc
> > > > On Error Resume Next
> > > > str = rng.Name.Name
> > > > If Err.Number Then
> > > > str = "Unamed"
> > > > Err.Clear
> > > > End If
> > > > MsgBox rng.Address & vbCr & str
> > > > End Sub
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > > > news:e73SWda8EHA.1264@TK2MSFTNGP12.phx.gbl...
> > > > > Is there an easy way to get the name of the range containing the
> > > > ActiveCell?
> > > > > I have a macro that loops through all the names in the workbook
and
> > sees
> > > > if
> > > > > they intersect with the ActiveCell - but isn't there an easier
way?
> I
> > > > tried
> > > > > ActiveCell.Range.Name, and getting the row and column references
for
> > > > > Cells(r,c).Range.Name, but nothing worked.
> > > > >
> > > > > Ed
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

I find the easiest way to find out if a cell is part of a named range is to use
Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

(ok, that doesn't help if you need it in the middle of your code--but it's still
a very useful addin.)



Ed wrote:
>
> Is there an easy way to get the name of the range containing the ActiveCell?
> I have a macro that loops through all the names in the workbook and sees if
> they intersect with the ActiveCell - but isn't there an easier way? I tried
> ActiveCell.Range.Name, and getting the row and column references for
> Cells(r,c).Range.Name, but nothing worked.
>
> Ed

--

Dave Peterson