Range Name (XP)

Topic

New Reply

(VBA) I need to determine what range a selected cell is in. For example, if I select cell S22 and it is in Range “Test” I need to return the name of the range.
Thanks for the help.

Reply | Quote

Replies

Try this function. It will find one of the names (if it contains multiple names). If you pass more than 1 cell as the range name, it will give a response if ANY of the cells are in a name. It could be modified to insist that the ENTIRE range is within the name or even that only the upper left cell. You could even write a routine to have ALL the names listed!

Steve

Option Explicit
Function InName(rng As Range)
    Dim nName As Name
    Dim isect
    
    InName = CVErr(xlErrNA)
    
    For Each nName In ActiveWorkbook.Names
        If Not Intersect(rng, nName.RefersToRange) Is Nothing Then
            InName = nName.Name
            Exit Function
        End If
    Next nName
End Function

Reply | Quote

The following function should return the name of the range to which given cell (or range of cells) belongs. Given that a such a range may belong to more than one named range, the function returns a list of names, where appropriate, seperated by a semicolon.

Function RangeName(rngInput As Range) As String
Dim oName As Name, strSep As String
For Each oName In ActiveWorkbook.Names
If Len(RangeName) Then strSep = “; ”
If Not Intersect(Range(oName), rngInput) Is Nothing Then
RangeName = RangeName & strSep & oName.Name
End If
Next
End Function

EDIT :

To allow using the function across workbooks, replace

For Each oName In ActiveWorkbook.Names

with

For Each oName In rngInput.Parent.Parent.Names

Andrew C

Reply | Quote