Cell is a Member of a range or Not

  • Thread starter Thread starter pgjoshi
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top