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

Name of range containing ActiveCell?
 

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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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
...
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" wrote in message
...
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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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
...
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" wrote in message
...
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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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
...
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" wrote in message
...
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

Name of range containing ActiveCell?
 

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