About Us

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" wrote in message
...
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"

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA - Determine last cell in range?	Noozer	Excel Programming	2	February 24th 06 08:59 PM
Value of cell to determine range in MAX Function	[email protected]	Excel Worksheet Functions	8	February 18th 06 06:34 PM
Determine if Cell Address is within a Range	John Michl	Excel Worksheet Functions	4	December 22nd 05 07:59 PM
Can VBA determine if a cell/range is in a group?	BlindGuardian	Excel Programming	7	June 10th 05 08:12 PM
Can VBA determine if a cell/range is in a group?	Ron Coderre[_11_]	Excel Programming	0	June 10th 05 06:49 PM

Menu

About Us