intersect

[ranswert]

I have been trying to write a procedure that checks if a selected cell is in
a range of cells.
I wrote the following code:

Sub intersct()
Dim xcell As Range
Dim a As String
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range

Set xcell = ActiveCell.EntireRow.Cells(1)
a = xcell.Text
Do While a = ""
Set xcell = xcell.Offset(-1, 0)
a = xcell.Text
Loop
costid = a
MsgBox (costid)
Set rng1 = Range(costid & "estsubvenrng")
Set rng2 = Range(costid & "estdescriptrng")
Set rng3 = Range(costid & "estamountrng")
Set isect = Application.Intersect(rng1, rng2, rng3)
If isect Is Nothing Then
MsgBox "nothing"
Else
MsgBox "Intersect"
End If



End Sub

When I run it I always get msgbox "Nothing" even when the selected cell is
within the range of cells.

What am I doing wrong?
Thanks

 

[JLGWhiz]

You have these declared as type Range:

Set rng1 = Range(costid & "estsubvenrng")
Set rng2 = Range(costid & "estdescriptrng")
Set rng3 = Range(costid & "estamountrng")

but the construction of the set statement does not appear to equate to a
range reference, but I can't be sure because "estsubvenrng" and the other two
are not declared anywhere. Also, costid in this code could be a variable
declared on the fly or it could be a range name on the worksheet, again
undetermined by info provided.

 

[ranswert]

estsubvenrng and the other two are named ranges in my worksheet. Costid is
part of the name. For example: when costid = cst01 it refers to the named
range "cst01estsubvenrng". cst01 identifies which "estsubvenrng" to look at.
I am trying to make sure that a procedure doesn't execute unless a cell in
one of the three ranges is selected.

 

[JLGWhiz]

Ok, for the Set isect statement to return a true value, one of the arguments
must intersect with one of the other arguments. So if the three arguments
that you are using are ranges that parallel each other and do not cross at
some point, you will get a nothing. I am trying to determine exactly what
you want to return to suggest what other argument to add to the Intersect
function, but there is not enough info to do that. But maybe you can figure
that out.

 

[JLGWhiz]

P.S. In the last Message Box you should use the variable isect instead of
"Intersect" to return the value of the Intersect.

 

[JLGWhiz]

After further review, it looks like the original ActiveCell is the cell you
want to check, so if you add that to your Intersect arguments, it should give
you a return.

Set isect = Application.Intersect(ActiveCell, rng1, rng2, rng3)

 

[Dave Peterson]

So it could be in one of named ranges or any two of the named ranges or all
three of the named ranges (if those names overlap)??

You really don't care which range the activecell is in--you just want to know if
it's in one of them?

Option Explicit
Sub intersct()

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim CostId As String

CostId = "hi"

With ActiveSheet
Set rng1 = .Range(CostId & "estsubvenrng")
Set rng2 = .Range(CostId & "estdescriptrng")
Set rng3 = .Range(CostId & "estamountrng")
End With

If Intersect(ActiveCell, rng1) Is Nothing _
And Intersect(ActiveCell, rng2) Is Nothing _
And Intersect(ActiveCell, rng3) Is Nothing Then
MsgBox "Not in any of the 3 ranges!"
Else
MsgBox "activecell is in at least one of those ranges!"
End If

End Sub

You could also use:

If Intersect(ActiveCell, Union(rng1, rng2, rng3)) Is Nothing Then
MsgBox "Not in any of the 3 ranges!"
Else
MsgBox "activecell is in at least one of those ranges!"
End If

 

[Rick Rothstein \(MVP - VB\)]

If Intersect(ActiveCell, rng1) Is Nothing _

And Intersect(ActiveCell, rng2) Is Nothing _
And Intersect(ActiveCell, rng3) Is Nothing Then

I've not had much occasion to use these functions, but I'm guessing from
their help file write that the above could be replaced with this...

If Intersect(ActiveCell, Union(rng1, rng2, rng3)) Is Nothing Then

Rick

 

[JLGWhiz]

The help file is misleading in that it states that you can have two or more
range arguments. You can, so long as they have a common intersection.
Otherwise, you get a block variable not set message and the Intersect
variable equals nothing.
This is one that is useful in worksheet change events for a cell in a range
of cells, but gets a little unweildly outside of that.

 

[Dave Peterson]

Excellent point.

That's why I included this portion at the end:

You could also use:

If Intersect(ActiveCell, Union(rng1, rng2, rng3)) Is Nothing Then
MsgBox "Not in any of the 3 ranges!"
Else
MsgBox "activecell is in at least one of those ranges!"
End If

Or did you just copy that from my post??? <vbg>

 

[Rick Rothstein \(MVP - VB\)]

LOL

Note to self: You paid for a mouse with a scroll wheel... you should try
using the damn thing every now and then.

Rick

 

[Dave Peterson]

I know the feeling. I hate when I respond to a message and then scroll down to
see 18 other, er, better, replies.

But you should get a mouse with a scroll wheel! <gd&r>

 

[ranswert]

Thanks for all the help. I know where the mistake was.