INTERSECT function
Posted by Minh on December 23, 2001 10:14 PM
I always have problem with INTERSECT function.
I have the following code which always return
MsgBox "NOT in the list",
With another version of Excel in another computer
The error1004 always give Object variable or with block variable not set
Dim isect As Range
Worksheets("Data").Activate
Set isect = Application.Intersect(ActiveCell, Range("G4:G8"))
If isect Is Nothing Then
MsgBox "NOT in the list
Else
MsgBox "IS in the list"
End If
Can any one help !
Minh
Posted by Damon Ostrander on December 24, 2001 12:42 AM
Hi Minh,
Wow! I can't see how anything can go wrong with this code. It works fine for me. Are you sure that the active cell on the Data worksheet is in the range G4:G8? If prior to execution of this code some other sheet is active, then the cell that will become active when the Data worksheet is activated will be the one that was active at the time the Data sheet was last de-activated. Does this help?
Damon
Posted by Ivan F Moala on December 24, 2001 1:03 AM
What version of Excel and How are you activation this macro....via commandbutton ??
Ivan
Posted by Joe Was on December 26, 2001 1:47 PM
Your not the only one
I tried your code and the Excel Help code, which is much the same, on Excel 2000. Tried named ranges, striped code, hard coded ranges and other gyrations, the best it would do is return "Not in list" even if the selected cell was a call to the list! So, I would say that under certain PC configurations the "Intersect" method does not work. I will try the code again with Excel XP at home tonight, as this is strange.
I have used the Intersect method in search and sort code before and it worked, I don't understand why this simple code does not work? JSW
