Determine if a cell is in a range?
Thank You. Worked perfectly
"Tom Ogilvy" wrote:
Public Function IsInName(rng As Range, rng1 As Range)
If rng1.Parent.Name = rng.Parent.Name Then
If Not Intersect(rng1, rng) Is Nothing Then
IsInName = True
Exit Function
End If
End If
IsInName = False
End Function
Placed in a general module
usage
=IsInName(B9,Jan)
--
Regards,
Tom Ogilvy
"fedude" wrote:
Tom,
This is close, but what I would like to do is pass in a range name and a
cell reference. For instance:
=IsInName(B9, Jan)
"Tom Ogilvy" wrote:
There is not built in worksheet function that will reveal this information.
You could build a User Defined function in VBA and have it loop through the
list of names and determine whether the cell in question is included in any
of them.
Is that what you are looking for?
Public Function IsInName(rng as Range)
for each nm in ThisWorkbook.Names
on Error resume next
set rng1 = nm.ReferstoRange
on Error goto 0
if not rng1 is nothing then
if rng1.Parent = rng.Parent then
if not intersect(rng1,rng) is nothing then
isInName = nm.Name
exit function
end if
end if
end if
Next
IsInName = False
End Function
Place in a general module
usage
=IsInName(B9)
--
Regards,
Tom Ogilvy
"fedude" wrote:
Is there a way to determine if a single cell is in a named array?
For instance,
In B7, I want to know if B3 is in a range called "Jan"
|