Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine if a cell is within a named range

    Hi,

    This is something I've just written because I certainly have a use for it.

    I often have a need to now if a particular cell is within a named range.

    Because I'm not so great on the user side of Excel, where there's probably an inbuilt function to do it, I've written a small routine, which for a cell which I've sleected, tells me any named ranges that cell occurs in.

    <pre>Sub ListNamedRanges()
    Dim xlnName As Excel.Name
    Dim rngNamedRange As Range
    Dim rngcell As Range
    Dim strOutput As String
    Set rngcell = Selection(1, 1)
    For Each xlnName In ActiveWorkbook.Names
    On Error Resume Next
    Set rngNamedRange = xlnName.RefersToRange
    If rngNamedRange.Parent.Name = ActiveSheet.Name Then
    If Union(rngNamedRange, rngcell).Address = rngNamedRange.Address Then
    strOutput = strOutput & vbCrLf & xlnName.Name
    End If
    End If
    Next
    MsgBox strOutput
    End Sub
    </pre>


    Acknowledgements to John Walkenbach- "Excel 2000 Power Programming with VBA" for using "union" to determine if one range is within another.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine if a cell is within a named range

    Geoff,

    I don't think there is a built in function. If anyone knows better, I would love to be corrected.

    Brooke

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Determine if a cell is within a named range

    Am fairly certain there's no built-in function.
    If you wanted to know if a cell was part of one particular named range, then you could use a custom function as follows:

    Private Function WithinRange(rngCell As Excel.Range, rngName As Excel.Range) As Boolean
    If Intersect(rngCell.Resize(1, 1), rngName) Is Nothing Then
    WithinRange = False
    Else
    WithinRange = True
    End If
    End Function

    Then in an empty cell you'd enter something like =WithinRange(A1, NamedRange)
    which would display either TRUE or FALSE depending on whether or not cell A1 is part of NamedRange.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •