VBA test if cell is in a range

Question

I want to test if a given cell is within a given range in Excel VBA  What is the best way to do this

User · Answer

I don t work with contiguous ranges all the time  My solution for non-contiguous ranges is as follows  includes some code from other answers here    Sub test inters       Dim rng1 As Range     Dim rng2 As Range     Dim inters As Range      Set rng2   Worksheets  Gen2   Range  K7       Set rng1   ExcludeCell Worksheets  Gen2   Range  K6 K8    rng2       If  rng2 Parent name   rng1 Parent name  Then         Dim ints As Range         MsgBox rng1 Address  amp  vbCrLf            amp  rng2 Address  amp  vbCrLf            For Each cell In rng1             MsgBox cell Address             Set ints   Application Intersect cell  rng2              If  Not  ints Is Nothing   Then                 MsgBox  Yes intersection              Else                 MsgBox  No intersection              End If         Next cell     End If End Sub

User · Answer

If the two ranges to be tested  your given cell and your given range  are not in the same Worksheet  then Application Intersect throws an error  Thus  a way to avoid it is with something like  Sub test inters rng1 As Range  rng2 As Range      If  rng1 Parent Name   rng2 Parent Name  Then         Dim ints As Range         Set ints   Application Intersect rng1  rng2          If  Not  ints Is Nothing   Then               Do your job         End If     End If End Sub

User · Answer

Here is another option to see if a cell exists inside a range  In case you have issues with the Intersect solution as I did   If InStr range  NamedRange   Address  range  IndividualCell   Address   gt  0 Then      The individual cell exists in the named range Else      The individual cell does not exist in the named range End If   InStr is a VBA function that checks if a string exists within another string   https   msdn microsoft com en-us vba language-reference-vba articles instr-function

User · Answer

Determine if a cell is within a range using VBA in Microsoft Excel   From the linked site  maintaining credit to original submitter       VBA macro tip contributed by Erlandsen Data Consulting   offering Microsoft Excel Application development  template customization    support and training solutions   Function InRange Range1 As Range  Range2 As Range  As Boolean       returns True if Range1 is within Range2     InRange   Not  Application Intersect Range1  Range2  Is 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

User · Answer

From the Help     Set isect   Application Intersect Range  rg1    Range  rg2    If isect Is Nothing Then     MsgBox  Ranges do not intersect  Else     isect Select End If

User · Answer

mywolfe02 gives a static range code so his inRange works fine but if you want to add dynamic range then use this one with inRange function of him this works better with when you want to populate data to fix starting cell and last column is also fixed    Sub DynamicRange    Dim sht As Worksheet Dim LastRow As Long Dim StartCell As Range Dim rng As Range  Set sht   Worksheets  xyz   LastRow   sht Cells Find      SearchOrder  xlByRows  SearchDirection  xlPrevious  row Set rng   Workbooks  Record xlsm   Worksheets  xyz   Range Cells 12  2   Cells LastRow  12    Debug Print LastRow  If InRange ActiveCell  rng  Then          MsgBox  Active Cell In Range     Else       MsgBox  Please select the cell within the range     End If  End Sub

[excel] VBA test if cell is in a range

Examples related to excel

Examples related to vba