VBA - is this cell in a given range?
VBA - is this cell in a given range?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA - is this cell in a given range?

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I'm struggling to find, through VBA, how one can find out if a cell is within a range, and then return that range name. For example if cell A1 is within a larger range of "A1:A20" named "RANGE1", how do you retrieve that name through code?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Something like EDC's test work here?

    Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
    End Function


    Sub TestInRange()
    If InRange(ActiveCell, Range("A1:D100")) Then
    ' code to handle that the active cell is within the right range
    MsgBox "Active Cell In Range!"
    Else
    ' code to handle that the active cell is not within the right range
    MsgBox "Active Cell NOT In Range!"
    End If
    End Sub

    HTH. Cheers, Nate

  3. #3
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try something like this:

    Code:
    Sub RangeNames()
        Dim nm As Name
        
        For Each nm In ActiveWorkbook.Names
            If Not Intersect(Range("C3"), nm.RefersToRange) Is Nothing Then
                MsgBox nm.Name
            End If
        Next
    End Sub
    Hope this helps,

    Russell

  4. #4
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I appreciate your fast reply, but I think the code you gave me assumes I know the name of the range I'm trying to find. My problem is that I need to retrieve the name of the range that the cell is located in. I know the cell will be contained in a range. I just don't know which one.

  5. #5
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-02-28 08:54, mbahr2 wrote:
    I appreciate your fast reply, but I think the code you gave me assumes I know the name of the range I'm trying to find. My problem is that I need to retrieve the name of the range that the cell is located in. I know the cell will be contained in a range. I just don't know which one.
    See my post - it should work for you.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com