Excel VBA Intersect

Question

I found this code online and apparently it works for other people but not me? I don't know where it's wrong. I made a simple example and have my Range1 and Range 2 to be certain cells in excel, enter image description here

Also, I would like to know if there is a way to return the intersections, if it can. Thanks in advance!

Function InRange(Range1 As Range, Range2 As Range) As Boolean
    Set intersectRange = Application.Intersect(Range1, Range2)
    If intersectRange Is Nothing Then
        InRange = False
    Else
        InRange = True
    End If
End Function

What exactly are you trying to achieve with this code, and what results are you currently getting? Can you create a Sub to call this function and step by step through the function to see what it's doing? — TMH8885, May 21 '15 at 19:52

score 9 · Accepted Answer · answered May 21 '15 at 20:07

9

It seems to me that you are expecting Intersect to check whether the two ranges have cells with the same values? Is that the point from having the value "a" in both S15 and T15?

If so, then this is why you are not getting what you expect. The Intersect function returns a range that is the "overlap" of the two ranges, regardless of the values inside each.

As this is my first post on SE, I hope this helps :)

answered May 21 '15 at 20:07

A.S.H

29,101
5
23
50

you are right, stupid me, I expected it to work just like the Matlab Function intersect, which gives you the "overlap". Got it, thanks. – Ogre Magi May 21 '15 at 20:11
1

Good catch; I completely overlooked the misuse of the [Intersect method](https://msdn.microsoft.com/en-us/library/office/aa195772%28v=office.11%29.aspx). – May 21 '15 at 20:13

score 3 · Answer 2 · answered May 21 '15 at 20:10

There is nothing fundamentally wrong with your function but I would declare the intersectRange variable as a range. If you wanted to return the intersect range, you can do that directly from intersectRange is not nothing and return a variant from the function.

Function InRange(Range1 As Range, Range2 As Range) As Variant
    Dim intersectRange As Range
    Set intersectRange = Application.Intersect(Range1, Range2)
    If intersectRange Is Nothing Then
        InRange = False
    Else
        InRange = intersectRange.Address(0, 0)
    End If
End Function

Excel VBA Intersect

2 Answers2

Linked