Determine if a cell is in a range?

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"

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"

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

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

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"

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"

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"

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"

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"