Need a little bit of help using Intersect of two cells to get the value from the result

[OaklandJim]

As usual, I start by saying "I must be missing something basic." I have a function that looks in a 1 row horizontal range for a group name and for a date in a one column vertical range.

I need to know what value is at the intersection of the two cells. But I cannot figure out how to use Intersect function. It does not crash its just that if I try to do anything with the range (resulting from the call to the Intersect function) I get a crash. I tried to test for Intersection(GroupCell, DateCell) Is Nothing but using debug.print but that doesn't work.

BTW All examples that I see have multicell ranges. Is that my issue, my range objects are one cell?

Code segment is below. The outputs for the in-line use of debug.print are shown too.

Debug.Print "rGroup = " & rGroup.Address

Debug.Print "rDate = " & rDate.Address

Debug.Print "Before"
    
    Set rCell = Intersect(rGroup, rDate)

Debug.Print "After"

Debug.Print "rCell Is Nothing = " & rCell Is Nothing
    
Debug.Print "Intersect rCell = " & rCell.Address

rGroup = $Q$1
rDate = $P$4
Before
After

 

[OaklandJim]

I was mistaken about how Intersect works. There is no intersection of the ranges I was testing. All is well with Intersect.

 

[smozgur]

I was mistaken about how Intersect works. There is no intersection of the ranges I was testing. All is well with Intersect.

Correct.

When the Application.Intersect method is used, you need to test the intersection range - rCell - and decide how your macro should proceed as shown below.

Sub testIntersect()
Dim rGroup As Range
Dim rDate As Range
Dim rCell As Range
    Set rGroup = Range("A1:B4")
    Set rDate = Selection
    Set rCell = Intersect(rGroup, rDate)
    If rCell Is Nothing Then
        MsgBox "No intersection"
    Else
        MsgBox rCell.Address
    End If
End Sub