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

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

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

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

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

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

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

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

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

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