intersect
 

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

intersect
 

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" wrote:

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

intersect
 

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" wrote:

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" wrote:

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

intersect
 

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.

"ranswert" wrote:

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" wrote:

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" wrote:

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

intersect
 

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

"ranswert" wrote:

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

intersect
 

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)


"ranswert" wrote:

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" wrote:

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" wrote:

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

intersect
 

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

ranswert wrote:

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

--

Dave Peterson

intersect
 

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

intersect
 

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.

"Rick Rothstein (MVP - VB)" wrote:

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

intersect
 

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)" wrote:

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

--

Dave Peterson

intersect
 

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" wrote in message
...
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)" wrote:

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

--

Dave Peterson

intersect
 

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)" wrote:

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

--

Dave Peterson

--

Dave Peterson

intersect
 

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

"Dave Peterson" wrote:

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

ranswert wrote:

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

--

Dave Peterson