Application.Intersect

N

notprovided

Why doesn't this work?

Straight from the microsoft site, or the help files for
Application.Intersect

Worksheets("Sheet1").Activate
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If

I opened a new workbook, named two ranges which must intersect per the
procedure, and it displays the messagebox every time. Doesn't matter
if there's a value in the intersected cell or not.
 
L

Lars-Åke Aspelin

Why doesn't this work?

Straight from the microsoft site, or the help files for
Application.Intersect

Worksheets("Sheet1").Activate
Set isect = Application.Intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If

I opened a new workbook, named two ranges which must intersect per the
procedure, and it displays the messagebox every time. Doesn't matter
if there's a value in the intersected cell or not.


Did you really manage to name the ranges to "rg1" and "rg2"?
I think "rg1" and "rg2" are not valid range name as they conflict with
the cells with the same name.

It you use e.g. "rg_1" and "rg_2" for the range names the result will
be as expected.

Hope this helps. / Lars-Åke
 
B

Bernard Liengme

You did not tell us but I suspect you are using Excel 2007
The code works in XL2003 but not in XL2007
Why? because RG1 and RG2 are valid cell references in XL2007
Rename your ranges as myrg1 and myrg2; fix the code to reflect this and all
will be well
 
N

notprovided

You did not tell us but I suspect you are using Excel 2007
The code works in XL2003 but not in XL2007
Why? because RG1 and RG2 are valid cell references in XL2007
Rename your ranges as myrg1 and myrg2; fix the code to reflect this and all
will be well

XL2003

And no, I wasn't able to name the ranges. It appeared I had, which is
why I thought I had, but when I opened the names drop down they
weren't there. I did figure out how to make it work though.

I was looking for the 'apparent' intersection of two cells, assuming
there's an apparent intersection of any two cells, and found it.

It works like this.

Set isect = Application.Intersect(Range("A11").EntireRow,_
Range("D4").EntireColumn)
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
MsgBox isect
End If
End Sub

Thanks for your input. It was my misunderstanding of how it worked.

Note that it didn't work with single named cells without the
..EntireRow and .EntireColumn, but did work with named blocks of cells
which did in fact intersect.

Thanks for the input.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Intersect for time 2
intersect method error 5
Intersect & Hyperlink 2
Add a message to this array. 7
intersect 12
HOW TO DO AN IF, IN A MACRO 19
If one range changes, update the other 18
Change event and ranges 4

Top