Application.Intersect

[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.

 

[Wigi]

Do the 2 named ranges exist in your sheet?

 

[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

 

[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

 

[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.