Compare what cell is stored in a Range variable.

**GaidenFocus** · 07-12-2012, 11:17 AM

How would i get something like the following code to work? Right now it returns true no matter what, when this should always be false.

Sub test()
Dim tmpbox As Range
Dim finder As Range

Set tmpbox = Range("a1")

Set finder = Range("a3")

If tmpbox.Offset(1, 0) = finder Then
MsgBox "True"
Else
MsgBox "False"
End If

End Sub

**tigeravatar** · 07-12-2012, 11:21 AM

GaidenFocus,

Right now that code is checking the values against each other. If both A2 and A3 are blank, it will return True. To see if a cell is part of another range, you'll need to use the Intersect method.

**romperstomper** · 07-12-2012, 11:23 AM

If tmpbox.Offset(1, 0).address(external:=True) = finder.address(external:=True) Then

or

If Not Intersect(tmpbox.Offset(1, 0), finder) Is Nothing Then

since both are single cells

**StevenM** · 07-12-2012, 11:24 AM

It works perfect for me.

If tmpbox.Offset(1, 0) = finder Then

You are here asking if the value in A2 = the value in A3.

When I ran the code, if A2 = A3 then the msgbox gave "True" else "False"!

**GaidenFocus** · 07-12-2012, 03:05 PM

Thanks romper and tiger the intersect method worked just like i needed it to. Steve, i was not trying to compare the values in the cells, i was trying to compare the range stored in the variable.