software

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 quot;rangequot;

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) --gt; 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).

quot;Adam Kroger
gt;
gt; Is there a way to return, via a function, the name of the range a cell
gt; resides in
gt; example:
gt; range = A1:C10
gt;
gt; I want a formula that, when put in cell B5, will return quot;rangequot;
gt;
gt; ans extra bonus would be one that can return the name of the range that
gt; another cell resides in.
gt; Example
gt; Range2 - A12:C21
gt;
gt; B2 is =whatever_formula(A15) --gt; Range2
gt;
gt; thanks

--

Dave Peterson

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

Adam

quot;Dave Petersonquot; gt; wrote in message
...
gt; This will return the first named range that it finds. A cell can be in
gt; lots of
gt; different named ranges.
gt;
gt; Option Explicit
gt; Function myName(rng As Range) As Variant
gt;
gt; Application.Volatile True
gt;
gt; Dim nm As Name
gt; Dim testRng As Range
gt;
gt; Set rng = rng.Cells(1)
gt;
gt; myName = CVErr(xlErrRef)
gt; For Each nm In Application.Caller.Parent.Parent.Names
gt; Set testRng = Nothing
gt; On Error Resume Next
gt; Set testRng = nm.RefersToRange
gt; On Error GoTo 0
gt;
gt; If testRng Is Nothing Then
gt; 'do nothing
gt; Else
gt; If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
gt; If rng.Parent.Name = testRng.Parent.Name Then
gt; If Intersect(rng, testRng) Is Nothing Then
gt; 'keep looking
gt; Else
gt; myName = nm.Name
gt; Exit For
gt; End If
gt; End If
gt; End If
gt; End If
gt; Next nm
gt;
gt; End Function
gt;
gt; =myName(a1)
gt; in any cell (including A1).
gt;
gt; quot;Adam Kroger
gt;gt;
gt;gt; Is there a way to return, via a function, the name of the range a cell
gt;gt; resides in
gt;gt; example:
gt;gt; range = A1:C10
gt;gt;
gt;gt; I want a formula that, when put in cell B5, will return quot;rangequot;
gt;gt;
gt;gt; ans extra bonus would be one that can return the name of the range that
gt;gt; another cell resides in.
gt;gt; Example
gt;gt; Range2 - A12:C21
gt;gt;
gt;gt; B2 is =whatever_formula(A15) --gt; Range2
gt;gt;
gt;gt; thanks
gt;
gt; --
gt;
gt; 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 www.oaltd.co.uk/mvp
quot;Adam Kroger
gt;
gt; Dave:
gt; Thank you, works like a dream. Fixed a problem that had been eluding me for
gt; a while.
gt;
gt; Adam
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; This will return the first named range that it finds. A cell can be in
gt; gt; lots of
gt; gt; different named ranges.
gt; gt;
gt; gt; Option Explicit
gt; gt; Function myName(rng As Range) As Variant
gt; gt;
gt; gt; Application.Volatile True
gt; gt;
gt; gt; Dim nm As Name
gt; gt; Dim testRng As Range
gt; gt;
gt; gt; Set rng = rng.Cells(1)
gt; gt;
gt; gt; myName = CVErr(xlErrRef)
gt; gt; For Each nm In Application.Caller.Parent.Parent.Names
gt; gt; Set testRng = Nothing
gt; gt; On Error Resume Next
gt; gt; Set testRng = nm.RefersToRange
gt; gt; On Error GoTo 0
gt; gt;
gt; gt; If testRng Is Nothing Then
gt; gt; 'do nothing
gt; gt; Else
gt; gt; If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
gt; gt; If rng.Parent.Name = testRng.Parent.Name Then
gt; gt; If Intersect(rng, testRng) Is Nothing Then
gt; gt; 'keep looking
gt; gt; Else
gt; gt; myName = nm.Name
gt; gt; Exit For
gt; gt; End If
gt; gt; End If
gt; gt; End If
gt; gt; End If
gt; gt; Next nm
gt; gt;
gt; gt; End Function
gt; gt;
gt; gt; =myName(a1)
gt; gt; in any cell (including A1).
gt; gt;
gt; gt; quot;Adam Kroger
gt; gt;gt;
gt; gt;gt; Is there a way to return, via a function, the name of the range a cell
gt; gt;gt; resides in
gt; gt;gt; example:
gt; gt;gt; range = A1:C10
gt; gt;gt;
gt; gt;gt; I want a formula that, when put in cell B5, will return quot;rangequot;
gt; gt;gt;
gt; gt;gt; ans extra bonus would be one that can return the name of the range that
gt; gt;gt; another cell resides in.
gt; gt;gt; Example
gt; gt;gt; Range2 - A12:C21
gt; gt;gt;
gt; gt;gt; B2 is =whatever_formula(A15) --gt; Range2
gt; gt;gt;
gt; gt;gt; thanks
gt; gt;
gt; gt; --
gt; gt;
gt; gt; 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)
quot;Dave Petersonquot; gt; wrote in message
...
gt; If you were trying to debug your workbook, you may want to get a copy of
gt; Jan
gt; Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
gt;
gt; You can find it at:
gt; NameManager.Zip from www.oaltd.co.uk/mvp
gt;
gt;
gt;
gt; quot;Adam Kroger
gt;gt;
gt;gt; Dave:
gt;gt; Thank you, works like a dream. Fixed a problem that had been eluding me
gt;gt; for
gt;gt; a while.
gt;gt;
gt;gt; Adam
gt;gt;
gt;gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; This will return the first named range that it finds. A cell can be in
gt;gt; gt; lots of
gt;gt; gt; different named ranges.
gt;gt; gt;
gt;gt; gt; Option Explicit
gt;gt; gt; Function myName(rng As Range) As Variant
gt;gt; gt;
gt;gt; gt; Application.Volatile True
gt;gt; gt;
gt;gt; gt; Dim nm As Name
gt;gt; gt; Dim testRng As Range
gt;gt; gt;
gt;gt; gt; Set rng = rng.Cells(1)
gt;gt; gt;
gt;gt; gt; myName = CVErr(xlErrRef)
gt;gt; gt; For Each nm In Application.Caller.Parent.Parent.Names
gt;gt; gt; Set testRng = Nothing
gt;gt; gt; On Error Resume Next
gt;gt; gt; Set testRng = nm.RefersToRange
gt;gt; gt; On Error GoTo 0
gt;gt; gt;
gt;gt; gt; If testRng Is Nothing Then
gt;gt; gt; 'do nothing
gt;gt; gt; Else
gt;gt; gt; If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
gt;gt; gt; If rng.Parent.Name = testRng.Parent.Name Then
gt;gt; gt; If Intersect(rng, testRng) Is Nothing Then
gt;gt; gt; 'keep looking
gt;gt; gt; Else
gt;gt; gt; myName = nm.Name
gt;gt; gt; Exit For
gt;gt; gt; End If
gt;gt; gt; End If
gt;gt; gt; End If
gt;gt; gt; End If
gt;gt; gt; Next nm
gt;gt; gt;
gt;gt; gt; End Function
gt;gt; gt;
gt;gt; gt; =myName(a1)
gt;gt; gt; in any cell (including A1).
gt;gt; gt;
gt;gt; gt; quot;Adam Kroger
gt;gt; gt;gt;
gt;gt; gt;gt; Is there a way to return, via a function, the name of the range a cell
gt;gt; gt;gt; resides in
gt;gt; gt;gt; example:
gt;gt; gt;gt; range = A1:C10
gt;gt; gt;gt;
gt;gt; gt;gt; I want a formula that, when put in cell B5, will return quot;rangequot;
gt;gt; gt;gt;
gt;gt; gt;gt; ans extra bonus would be one that can return the name of the range
gt;gt; gt;gt; that
gt;gt; gt;gt; another cell resides in.
gt;gt; gt;gt; Example
gt;gt; gt;gt; Range2 - A12:C21
gt;gt; gt;gt;
gt;gt; gt;gt; B2 is =whatever_formula(A15) --gt; Range2
gt;gt; gt;gt;
gt;gt; gt;gt; thanks
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; Dave Peterson
gt;
gt; --
gt;
gt; 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

quot;Adam Kroger
gt;
gt; 1 last quick question, what would I need to changeto make it look in the
gt; cell where it was called, with no argument.
gt;
gt; reason: Primary use I have in mind is to be called via a UseSameAS() UDF
gt; from another location in the workbook. I won't nesecarrily know the address
gt; of the cell that is calling it, and will use that function to find the name
gt; of the range it is in so that I can then use that name with a INDEX() to
gt; find another cell.
gt;
gt; CELL BA268 = INDEX(myName(),1,13)*$BA$260
gt; CELL ???? =UseSameAs(BA268)
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; If you were trying to debug your workbook, you may want to get a copy of
gt; gt; Jan
gt; gt; Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
gt; gt;
gt; gt; You can find it at:
gt; gt; NameManager.Zip from www.oaltd.co.uk/mvp
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Adam Kroger
gt; gt;gt;
gt; gt;gt; Dave:
gt; gt;gt; Thank you, works like a dream. Fixed a problem that had been eluding me
gt; gt;gt; for
gt; gt;gt; a while.
gt; gt;gt;
gt; gt;gt; Adam
gt; gt;gt;
gt; gt;gt; quot;Dave Petersonquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; This will return the first named range that it finds. A cell can be in
gt; gt;gt; gt; lots of
gt; gt;gt; gt; different named ranges.
gt; gt;gt; gt;
gt; gt;gt; gt; Option Explicit
gt; gt;gt; gt; Function myName(rng As Range) As Variant
gt; gt;gt; gt;
gt; gt;gt; gt; Application.Volatile True
gt; gt;gt; gt;
gt; gt;gt; gt; Dim nm As Name
gt; gt;gt; gt; Dim testRng As Range
gt; gt;gt; gt;
gt; gt;gt; gt; Set rng = rng.Cells(1)
gt; gt;gt; gt;
gt; gt;gt; gt; myName = CVErr(xlErrRef)
gt; gt;gt; gt; For Each nm In Application.Caller.Parent.Parent.Names
gt; gt;gt; gt; Set testRng = Nothing
gt; gt;gt; gt; On Error Resume Next
gt; gt;gt; gt; Set testRng = nm.RefersToRange
gt; gt;gt; gt; On Error GoTo 0
gt; gt;gt; gt;
gt; gt;gt; gt; If testRng Is Nothing Then
gt; gt;gt; gt; 'do nothing
gt; gt;gt; gt; Else
gt; gt;gt; gt; If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
gt; gt;gt; gt; If rng.Parent.Name = testRng.Parent.Name Then
gt; gt;gt; gt; If Intersect(rng, testRng) Is Nothing Then
gt; gt;gt; gt; 'keep looking
gt; gt;gt; gt; Else
gt; gt;gt; gt; myName = nm.Name
gt; gt;gt; gt; Exit For
gt; gt;gt; gt; End If
gt; gt;gt; gt; End If
gt; gt;gt; gt; End If
gt; gt;gt; gt; End If
gt; gt;gt; gt; Next nm
gt; gt;gt; gt;
gt; gt;gt; gt; End Function
gt; gt;gt; gt;
gt; gt;gt; gt; =myName(a1)
gt; gt;gt; gt; in any cell (including A1).
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Adam Kroger
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Is there a way to return, via a function, the name of the range a cell
gt; gt;gt; gt;gt; resides in
gt; gt;gt; gt;gt; example:
gt; gt;gt; gt;gt; range = A1:C10
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; I want a formula that, when put in cell B5, will return quot;rangequot;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; ans extra bonus would be one that can return the name of the range
gt; gt;gt; gt;gt; that
gt; gt;gt; gt;gt; another cell resides in.
gt; gt;gt; gt;gt; Example
gt; gt;gt; gt;gt; Range2 - A12:C21
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; B2 is =whatever_formula(A15) --gt; Range2
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; thanks
gt; gt;gt; gt;
gt; gt;gt; gt; --
gt; gt;gt; gt;
gt; gt;gt; gt; Dave Peterson
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson

--

Dave Peterson

excellent

Thank You

quot;Dave Petersonquot; gt; wrote in message
...
gt; Option Explicit
gt; Function myName(Optional rng As Range) As Variant
gt;
gt; Application.Volatile True
gt; Dim nm As Name
gt; Dim testRng As Range
gt;
gt; If rng Is Nothing Then
gt; Set rng = Application.Caller
gt; End If
gt;
gt; Set rng = rng.Cells(1)
gt;
gt; myName = CVErr(xlErrRef)
gt; For Each nm In Application.Caller.Parent.Parent.Names
gt; Set testRng = Nothing
gt; On Error Resume Next
gt; Set testRng = nm.RefersToRange
gt; On Error GoTo 0
gt;
gt; If testRng Is Nothing Then
gt; 'do nothing
gt; Else
gt; If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
gt; If rng.Parent.Name = testRng.Parent.Name Then
gt; If Intersect(rng, testRng) Is Nothing Then
gt; 'keep looking
gt; Else
gt; myName = nm.Name
gt; Exit For
gt; End If
gt; End If
gt; End If
gt; End If
gt; Next nm
gt;
gt; End Function
gt;
gt; quot;Adam Kroger
gt;gt;
gt;gt; 1 last quick question, what would I need to changeto make it look in the
gt;gt; cell where it was called, with no argument.
gt;gt;
gt;gt; reason: Primary use I have in mind is to be called via a UseSameAS() UDF
gt;gt; from another location in the workbook. I won't nesecarrily know the
gt;gt; address
gt;gt; of the cell that is calling it, and will use that function to find the
gt;gt; name
gt;gt; of the range it is in so that I can then use that name with a INDEX() to
gt;gt; find another cell.
gt;gt;
gt;gt; CELL BA268 = INDEX(myName(),1,13)*$BA$260
gt;gt; CELL ???? =UseSameAs(BA268)
gt;gt;
gt;gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; If you were trying to debug your workbook, you may want to get a copy
gt;gt; gt; of
gt;gt; gt; Jan
gt;gt; gt; Karel Pieterse's (with Charles Williams and Matthew Henson) Name
gt;gt; gt; Manager:
gt;gt; gt;
gt;gt; gt; You can find it at:
gt;gt; gt; NameManager.Zip from www.oaltd.co.uk/mvp
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Adam Kroger
gt;gt; gt;gt;
gt;gt; gt;gt; Dave:
gt;gt; gt;gt; Thank you, works like a dream. Fixed a problem that had been eluding
gt;gt; gt;gt; me
gt;gt; gt;gt; for
gt;gt; gt;gt; a while.
gt;gt; gt;gt;
gt;gt; gt;gt; Adam
gt;gt; gt;gt;
gt;gt; gt;gt; quot;Dave Petersonquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; This will return the first named range that it finds. A cell can be
gt;gt; gt;gt; gt; in
gt;gt; gt;gt; gt; lots of
gt;gt; gt;gt; gt; different named ranges.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Option Explicit
gt;gt; gt;gt; gt; Function myName(rng As Range) As Variant
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Application.Volatile True
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Dim nm As Name
gt;gt; gt;gt; gt; Dim testRng As Range
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Set rng = rng.Cells(1)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; myName = CVErr(xlErrRef)
gt;gt; gt;gt; gt; For Each nm In Application.Caller.Parent.Parent.Names
gt;gt; gt;gt; gt; Set testRng = Nothing
gt;gt; gt;gt; gt; On Error Resume Next
gt;gt; gt;gt; gt; Set testRng = nm.RefersToRange
gt;gt; gt;gt; gt; On Error GoTo 0
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; If testRng Is Nothing Then
gt;gt; gt;gt; gt; 'do nothing
gt;gt; gt;gt; gt; Else
gt;gt; gt;gt; gt; If rng.Parent.Parent.Name = testRng.Parent.Parent.Name
gt;gt; gt;gt; gt; Then
gt;gt; gt;gt; gt; If rng.Parent.Name = testRng.Parent.Name Then
gt;gt; gt;gt; gt; If Intersect(rng, testRng) Is Nothing Then
gt;gt; gt;gt; gt; 'keep looking
gt;gt; gt;gt; gt; Else
gt;gt; gt;gt; gt; myName = nm.Name
gt;gt; gt;gt; gt; Exit For
gt;gt; gt;gt; gt; End If
gt;gt; gt;gt; gt; End If
gt;gt; gt;gt; gt; End If
gt;gt; gt;gt; gt; End If
gt;gt; gt;gt; gt; Next nm
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; End Function
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; =myName(a1)
gt;gt; gt;gt; gt; in any cell (including A1).
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; quot;Adam Kroger
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; Is there a way to return, via a function, the name of the range a
gt;gt; gt;gt; gt;gt; cell
gt;gt; gt;gt; gt;gt; resides in
gt;gt; gt;gt; gt;gt; example:
gt;gt; gt;gt; gt;gt; range = A1:C10
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; I want a formula that, when put in cell B5, will return quot;rangequot;
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; ans extra bonus would be one that can return the name of the range
gt;gt; gt;gt; gt;gt; that
gt;gt; gt;gt; gt;gt; another cell resides in.
gt;gt; gt;gt; gt;gt; Example
gt;gt; gt;gt; gt;gt; Range2 - A12:C21
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; B2 is =whatever_formula(A15) --gt; Range2
gt;gt; gt;gt; gt;gt;
gt;gt; gt;gt; gt;gt; thanks
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; --
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Dave Peterson
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt;
gt;gt; gt; Dave Peterson
gt;
gt; --
gt;
gt; Dave Peterson