Intersecting addresses from two sheets
 

I am a newbie and I don't know the answer, but if you don't mind I
would like to add to the question. I have XP version.

I have tried this line
If Intersect(Sht.Range("$A$1"), Sht.UsedRange) Then
which fails at runtime - Type Mismatch.

But in the debug window
?Intersect(Sht.Range("$A$1"), Sht.UsedRange)
shows the contents of cell A1!

(Not only that...
Intersect(Sht.Cells(1, 1), Sht.UsedRange)
doesn't work in either! I thought both my args are ranges??)

So (pushing my luck) 2 questions:
1. I thought Intersect returned a range. Is the debug window just
showing the .Value as a convenience?
2. Why does the statement work in the debug window but fail in code?
(and why does the .Cells fail)

NOTE: Sht is not the active sheet. I figured that might be important!

Intersecting addresses from two sheets
 

You're treating that Intesect() expression as a boolean (= true or = false).

Try

if intersect(...) is nothing then
'nothing in common
else
'something in common
end if

1. Intersect will return a range or Nothing (depending)
2. And the watch window will show you the default property's value (.value for
range objects) as a help.
Barney Fife wrote:

I am a newbie and I don't know the answer, but if you don't mind I
would like to add to the question. I have XP version.

I have tried this line
If Intersect(Sht.Range("$A$1"), Sht.UsedRange) Then
which fails at runtime - Type Mismatch.

But in the debug window
?Intersect(Sht.Range("$A$1"), Sht.UsedRange)
shows the contents of cell A1!

(Not only that...
Intersect(Sht.Cells(1, 1), Sht.UsedRange)
doesn't work in either! I thought both my args are ranges??)

So (pushing my luck) 2 questions:
1. I thought Intersect returned a range. Is the debug window just
showing the .Value as a convenience?
2. Why does the statement work in the debug window but fail in code?
(and why does the .Cells fail)

NOTE: Sht is not the active sheet. I figured that might be important!

--

Dave Peterson