Cell is a Member of a range or Not

P

pgjoshi

I have defined a named range. I want to use VBA Code to know whether
particular address from sheet is a member of the defined named range o
not ?

Thanks in advance.

Prasad Josh
 
F

Frank Kabel

Hi
try the following (assumption: you have defined the name
'test_sect_name')
....
if intersect(Range("test_sect_name"),Range("A1")) is nothing then
msgbox "cell A1 not within name"
else
msgbox "cell A1 within name"
end if
 
G

Greg Wilson

Change ActiveCell to the desired cell and "myRange" to the
correct name.

Sub XXXX()
Dim IsInRange As Boolean
If Not Intersect(ActiveCell, Range("myRange")) _
Is Nothing Then IsInRange = True
MsgBox IsInRange
End Sub

Regards,
Greg
 
T

Tom Ogilvy

Your subject says cell implying a single cell, while your prose says address
which could mean multiple cells. If a single cell, intersect would work
fine. If multiple cells, intersect would not be definitive depending on
your definition of member - if member means a subset, the multiple cells and
and subset would be problematic for intersect. Union would be more
appropriate in this case.

if Union(Range(namedrange),Range(address)).address =
range(namedrange).address then
 
Top