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
|