Find Range Intersections without looping

[chuckjitsu]

Hello. Range A1:A5 is populated with range addresses as seen below. What I'm trying to do is see if another range address intersects any of those 5 range addresses and do this without looping. For example, let's say I wanted to check and see if range A7:A10 intersects any of the A1:A5 values below. I know I can loop through cells A1:A5 and compare their range values to range A7:A10 via INTERSECT to check for intersection. Is there a way to do this without looping through A1:A5, almost like using MATCH or FIND to look for intersections? Hopefully that makes sense. Let me know if more info is needed.

I don't really have any code to share relative to this as I just couldn't get my arms around potential syntax, if what I'm asking is even possible. Thanks in advance.


A1:B1
A1:A2
B1:C1
A2:A3
C1:D1

 

[Akuini]

Perhaps like this:

Dim tx As String
tx = Join(Application.Transpose(Range("A1:A5")), ",")

    If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then
        MsgBox "YES"
    Else
        MsgBox "NO"
    End If

 

[DanteAmor]

Other approach:

Sub Find_Intersections()
  Dim c As Range
  For Each c In Range("A1:A5")
    If Not Intersect(Range(c.Value), Range([C1].Value)) Is Nothing Then c.Offset(, 1).Value = "Yes"
  Next
End Sub

Result:

varios 16sep2021.xlsm
	A	B	C
1	A1:B1		A7:A10
2	A1:A2
3	B1:C1
4	A2:A3
5	A10:A11	Yes
Hoja4

 

[chuckjitsu]

Perhaps like this:

Dim tx As String
tx = Join(Application.Transpose(Range("A1:A5")), ",")

    If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then
        MsgBox "YES"
    Else
        MsgBox "NO"
    End If

Hi Akuini. Thanks for the response. When I ran your code, i got a 1004 error on this line: If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then

 

[chuckjitsu]

Other approach:

Sub Find_Intersections()
  Dim c As Range
  For Each c In Range("A1:A5")
    If Not Intersect(Range(c.Value), Range([C1].Value)) Is Nothing Then c.Offset(, 1).Value = "Yes"
  Next
End Sub

Result:

varios 16sep2021.xlsm
	A	B	C
1	A1:B1		A7:A10
2	A1:A2
3	B1:C1
4	A2:A3
5	A10:A11	Yes
Hoja4

Hi Dante. Thanks for the response. I was trying to see if this could be done without looping.

 

[Akuini]

Hi Akuini. Thanks for the response. When I ran your code, i got a 1004 error on this line: If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then

I ran this:

Sub try100()

Dim tx As String
tx = Join(Application.Transpose(Range("A1:A5")), ",")

    If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then
        MsgBox "YES"
    Else
        MsgBox "NO"
    End If

End Sub

on your sample:

Book1
	A
1	A1:B1
2	A1:A2
3	B1:C1
4	A2:A3
5	C1:D1
Sheet1

The message box appeared (it said "NO").
So no error.
Please try it in a clean sheet and the same example first.

 

[Fluff]

If you have blank cells in the A1:A5 range try this mod to Akuini's code

Dim tx As String
tx = Join(Application.Transpose(Range("A1:A5")), " ")
tx = Replace(Application.Trim(tx), " ", ",")

    If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then
        MsgBox "YES"
    Else
        MsgBox "NO"
    End If

 

[chuckjitsu]

I ran this:

Sub try100()

Dim tx As String
tx = Join(Application.Transpose(Range("A1:A5")), ",")

    If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then
        MsgBox "YES"
    Else
        MsgBox "NO"
    End If

End Sub

on your sample:

Book1
	A
1	A1:B1
2	A1:A2
3	B1:C1
4	A2:A3
5	C1:D1
Sheet1

The message box appeared (it said "NO").
So no error.
Please try it in a clean sheet and the same example first.

I ran this:

Sub try100()

Dim tx As String
tx = Join(Application.Transpose(Range("A1:A5")), ",")

    If Not Intersect(Range("A7:A10"), Range(tx)) Is Nothing Then
        MsgBox "YES"
    Else
        MsgBox "NO"
    End If

End Sub

on your sample:

Book1
	A
1	A1:B1
2	A1:A2
3	B1:C1
4	A2:A3
5	C1:D1
Sheet1

The message box appeared (it said "NO").
So no error.
Please try it in a clean sheet and the same example first.

Hi Akuini. You're code worked. It was human error (me) that caused the error message the first time around, so my apologies there and thanks again for providing a solution.