Name of range containing ActiveCell?

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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Yes, but that is not a mystery. So the code is working as Excel Works/Peter
designed it. As I said to Peter,
In other words, it works, but not the way he (Ed) wants it to work.

--
Regards,
Tom Ogilvy


"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:eT9L6xb8EHA.1596@tk2msftngp13.phx.gbl...
> 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Just to add to Tom's excellent advice, if you (Ed) reference your full named
range of 5x20 cells the routine should return the Name. But, as I mentioned
before, "providing the range is exactly a named range". But no way
"directly" to return the name of some named range that some cell is
somewhere inside of.

Regards,
Peter T

PS Good guess earlier from Tom as to why it wasn't working for Ed, verging
on the psychic!

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:u5bvyAc8EHA.1260@TK2MSFTNGP12.phx.gbl...
> Yes, but that is not a mystery. So the code is working as Excel
Works/Peter
> designed it. As I said to Peter,
> In other words, it works, but not the way he (Ed) wants it to work.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:eT9L6xb8EHA.1596@tk2msftngp13.phx.gbl...
> > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Another check you could do it you just want to know if it is in you named
range

if intersect(activecell,Range("MyRange")) is nothing then
Msgbox "Active cell is not in named range"
else
MsgBox "ActiveCell is in named Range"
End

This assumes the activecell and Myrange are at least on the same sheet.

--
Regards,
Tom Ogilvy


"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:eT9L6xb8EHA.1596@tk2msftngp13.phx.gbl...
> 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>