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"

You don't need a VBA function. You can do it with a formula:

=IF(ISERROR(F1 MyRange),"not in range","in range")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"fedude" <fedude@discussions.microsoft.com> wrote in message
news:A513A152-DD88-447F-B3E2-6A016305A204@microsoft.com...
> 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"

Very neat. Thanks!

"Chip Pearson" wrote:

> You don't need a VBA function. You can do it with a formula:
>
> =IF(ISERROR(F1 MyRange),"not in range","in range")
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "fedude" <fedude@discussions.microsoft.com> wrote in message
> news:A513A152-DD88-447F-B3E2-6A016305A204@microsoft.com...
> > 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"
>
>
>

Hi fedude,

1. Copy the code
2. Go to your workbook and press Alt + F11 to get into the VBA Editor.
3.In the VBA Editor go Insert>Module then paste the code into the new
module that appears.
4. Press Alt + F11 to get back to the normal Excel interface.
5. The function should appear in the list of All functions on the
Insert Function dialog after you go Insert>Function (select "All" in
the category box) as "IsInName"

As far as I am aware User Defined Functions never appear in the List of
Functions on the Insert Function dialog in the "Recently Used"
category.

Ken Johnson