Determine if a cell is in a range?

[fedude]

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"

[papou]

Hello
Programmatically?
MsgBox IIf(Intersect(Range("B3"), Range("jan")) Is Nothing, False, True)

HTH
Cordially
Pascal

"fedude" <fedude@discussions.microsoft.com> a écrit dans le message de news:
B71F320F-56C9-4FC0-9B13-37E3B74F817B@microsoft.com...
> 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"

[Ken Johnson]

If Not (Intersect(Range("B3"), Range("Jan")) Is Nothing) Then
Range("B7").Value = "B3 is in Jan"
Else: Range("B7").Value = "B3 is not in Jan"
End If

Ken Johnson

[Tom Ogilvy]

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"

[fedude]

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"

[fedude]

Pascal,

I was hoping to do this as a worksheet function. Either one I have to write
myself or one that already exists. I could not find intersect as one of the
builtin excel functions. Is there some way I can do this in a cell?

For instance:

=IsInRange(B9, Jan)

"papou" wrote:

> Hello
> Programmatically?
> MsgBox IIf(Intersect(Range("B3"), Range("jan")) Is Nothing, False, True)
>
> HTH
> Cordially
> Pascal
>
> "fedude" <fedude@discussions.microsoft.com> a écrit dans le message de news:
> B71F320F-56C9-4FC0-9B13-37E3B74F817B@microsoft.com...
> > 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"
>
>
>

[Tom Ogilvy]

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"

[fedude]

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"

[Chip Pearson]

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"

[fedude]

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"
>
>
>

[fedude]

Tom,

I'm new to VB. How do I place this in a "general module"?

"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"

[Ken Johnson]

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