Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Intersect | Excel Programming | |||
Intersect | Excel Programming | |||
Intersect Formula??? | Excel Programming | |||
Intersect Formula VBA Help | Excel Programming | |||
Help with If Not Intersect | Excel Programming |