Intersection in Excel VBA

[bobnotso]

I have two non-contiguous ranges which contain one common value. I want to use the intersection method to trigger a macro but this code does not do it. Any help please

Sheet7.Activate


With Sheets("Sheet7")
Set xrange = Sheets("sheet7").Range("A7:A9")
Set xcells = Sheets("sheet7").Range("Z8:Z10")
End With


For Each cell In xrange


If Not Application.Intersect(cell, xcells) Is Nothing Then

MsgBox "Hello"

End If
Next

 

[Eric W]

Welcome to the Board.

Those ranges do not intersect, so the MsgBox will never run. If you're looking to run it when it finds a common value in the 2 ranges, then something like:

    For Each cell1 in xrange
        For Each cell2 in xcells
            If cell1.Value = cell2.Value Then
                MsgBox "Hello"
            End If
         Next cell2
    Next cell1

 

[bobnotso]

Thanks for that Eric, I appreciate your help. Does this mean that the intersect method can never be used for non-contiguous ranges? In reality my ranges are much larger than the code I mentioned (I simplified it for the question) and I previously used a code similar to what you suggested and this was proving relatively slow. I was hoping intersect would be a faster process.

 

[Eric W]

Intersect only looks at the addresses of the ranges, it does not look at the contents, so if you try to Intersect 2 non-contiguous ranges, you will always get Nothing.

If you want to look at the contents of the ranges, you have a few options. First, the loop that I showed in the last message. But as you found out, if the range is large enough, it's pretty slow. That's because reading/writing to a worksheet is one of the slowest things you can do in VBA. Here are a couple of ways to speed it up:

Sub test9()
Dim xrange As Range, xcells As Range
Dim Cell1 As Range
Dim MyData1 As Variant, MyData2 As Variant, a1 As Long, a2 As Long, b1 As Long, b2 As Long

    Set xrange = Sheets("Sheet21").Range("C5:C10")
    Set xcells = Sheets("Sheet21").Range("I5:I10")
    
    For Each Cell1 In xrange
        Set matchcell = xcells.Find(Cell1.Value)
        If Not matchcell Is Nothing Then
            MsgBox Cell1.Address & " matches " & matchcell.Address
        End If
    Next Cell1
    
    MyData1 = xrange.Value
    MyData2 = xcells.Value
    
    For a1 = 1 To UBound(MyData1)
        For a2 = 1 To UBound(MyData1, 2)
            
            For b1 = 1 To UBound(MyData2)
                For b2 = 1 To UBound(MyData2, 2)
                
                    If MyData1(a1, a2) = MyData2(b1, b2) Then
                        MsgBox MyData1(a1, a2) & " is found in xcells"
                    End If
                Next b2
            Next b1
        
        Next a2
    Next a1
    
End Sub

The first part (in red) uses the built-in Find method. Built-in VBA functions are often much quicker.

The second part (in blue) reads boths ranges into internal arrays, then scans the arrays. Reading internal arrays is MUCH faster, you can often speed things up by a huge factor. It takes a bit more work, but is well worth it. For example, if you wanted to get the address of the matching cells, you'd need to add a1 to the row of the upper left cell in the range (xrange.Column), and a2 to the column of that cell (xrange.Row), and the same for the xcells ranges.

There are a few other more complicated tricks that are situational, so I'll leave them out for now.

Hope this helps!

 

[Jon Peltier]

Does this mean that the intersect method can never be used for non-contiguous ranges?

Your ranges are discontiguous, but more important for the terminology you've used (i.e., "intersection"), your two ranges are non-intersecting.