Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

This will return the first named range that it finds. A cell can be in lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

Dave:
Thank you, works like a dream. Fixed a problem that had been eluding me for
a while.

Adam

"Dave Peterson" wrote in message
...
This will return the first named range that it finds. A cell can be in
lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

If you were trying to debug your workbook, you may want to get a copy of 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



"Adam Kroger

Dave:
Thank you, works like a dream. Fixed a problem that had been eluding me for
a while.

Adam

"Dave Peterson" wrote in message
...
This will return the first named range that it finds. A cell can be in
lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

--

Dave Peterson

1 last quick question, what would I need to changeto make it look in the
cell where it was called, with no argument.

reason: Primary use I have in mind is to be called via a UseSameAS() UDF
from another location in the workbook. I won't nesecarrily know the address
of the cell that is calling it, and will use that function to find the name
of the range it is in so that I can then use that name with a INDEX() to
find another cell.

CELL BA268 = INDEX(myName(),1,13)*$BA$260
CELL ???? =UseSameAs(BA268)



"Dave Peterson" wrote in message
...
If you were trying to debug your workbook, you may want to get a copy of
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



"Adam Kroger

Dave:
Thank you, works like a dream. Fixed a problem that had been eluding me
for
a while.

Adam

"Dave Peterson" wrote in message
...
This will return the first named range that it finds. A cell can be in
lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range
that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

--

Dave Peterson

Option Explicit
Function myName(Optional rng As Range) As Variant

Application.Volatile True
Dim nm As Name
Dim testRng As Range

If rng Is Nothing Then
Set rng = Application.Caller
End If

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

"Adam Kroger

1 last quick question, what would I need to changeto make it look in the
cell where it was called, with no argument.

reason: Primary use I have in mind is to be called via a UseSameAS() UDF
from another location in the workbook. I won't nesecarrily know the address
of the cell that is calling it, and will use that function to find the name
of the range it is in so that I can then use that name with a INDEX() to
find another cell.

CELL BA268 = INDEX(myName(),1,13)*$BA$260
CELL ???? =UseSameAs(BA268)

"Dave Peterson" wrote in message
...
If you were trying to debug your workbook, you may want to get a copy of
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



"Adam Kroger

Dave:
Thank you, works like a dream. Fixed a problem that had been eluding me
for
a while.

Adam

"Dave Peterson" wrote in message
...
This will return the first named range that it finds. A cell can be in
lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range
that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

excellent

Thank You

"Dave Peterson" wrote in message
...
Option Explicit
Function myName(Optional rng As Range) As Variant

Application.Volatile True
Dim nm As Name
Dim testRng As Range

If rng Is Nothing Then
Set rng = Application.Caller
End If

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

"Adam Kroger

1 last quick question, what would I need to changeto make it look in the
cell where it was called, with no argument.

reason: Primary use I have in mind is to be called via a UseSameAS() UDF
from another location in the workbook. I won't nesecarrily know the
address
of the cell that is calling it, and will use that function to find the
name
of the range it is in so that I can then use that name with a INDEX() to
find another cell.

CELL BA268 = INDEX(myName(),1,13)*$BA$260
CELL ???? =UseSameAs(BA268)

"Dave Peterson" wrote in message
...
If you were trying to debug your workbook, you may want to get a copy
of
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



"Adam Kroger

Dave:
Thank you, works like a dream. Fixed a problem that had been eluding
me
for
a while.

Adam

"Dave Peterson" wrote in message
...
This will return the first named range that it finds. A cell can be
in
lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name
Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a
cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range
that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson