Tom,

I tried your test and I get a message stating:
"Cromwell refers to $L$2:$N$54"

It looks as though this is just confirming that the contents of C29 is a
valid Name range. I've now tried this on Excel 2K and Excel XP with the
same results. I'm not sure whats going on.
To test on a new worksheet, I number cells A2 through A27 (1 to 26). I then
created a Name Range in F2:F50 (named "test"). I typed "test" in C29.
Lastly, in various cells of F2:F50 I type some numbers but only some of them
matching the ones in A2:A27.
I now try my code and the result is A2:A27 are all selected. I don't
understand it. The code should only be selecting those cells from column A
that did not exist in the named range (in this case F2:F50). As stated
before, if I use the exact range instead of referring to a Named range, it
works fine. If it works fine for you but not me, I'm not sure where to go
from here.

Thanks for all the help. I'm hoping some light will be shed on my delima.

Thanks,
PCLIVE
Paul

"Tom Ogilvy" wrote in message
...
I'm with JMB - it looks like it should work if the value in C29 is a valid
range name. Run this test

Sub TestC29()
Dim rng as Range
On Error Resume Next
set rng = Range(Range("C29").Value)
On Error goto 0
if not rng is nothing then
msgbox Range("C29").Value & " refers to " & rng.Address
else
msgbox Range("C29").Value & " is not a valid range name"
end if
end sub

--
Regards,
Tom Ogilvy

"PCLIVE" wrote in message
...
Ok Tom.

I know you're probably getting tired of me. You've already tried to help
me
with this one a few times and things just don't go completely right on my
side. I really do appreciate your help and patients. Here is the full
code
that works without the defined Name reference.

Dim rng1 As Range, cell As Range
Dim res As Variant
With Worksheets("Branches")
Set rng1 = Nothing
End With

For Each cell In Range("A2:A27")
res = Application.Match(cell.Value, Range("L2:L50").Value, 0)
If IsError(res) Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Select
End If

However, if I use the code below, the "If IsError(res)" is true every
time
and therefore the end result is a selection of all the cells from A2:A27.
For some reason, when coded this way, there is never an Else for the "If
IsError(res)" statement. Any ideas.

Dim rng1 As Range, cell As Range
Dim res As Variant
With Worksheets("Branches")
Set rng1 = Nothing
End With

For Each cell In Range("A2:A27")
res = Application.Match(cell.Value, Range(Range("C29").Value), 0)
If IsError(res) Then
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.Select
End If


You're always a great help.
Thanks again,
Paul




"Tom Ogilvy" wrote in message
...
From the immediate window:
Range("C29").Value = "Name1"
Range("A1:A3").Value = application.Transpose(Array("A","B","C"))
res = Application.Match("B",Range(Range("C29").value),0)
? res
2

worked fine for me.

--
Regards,
Tom Ogilvy

"PCLIVE" wrote in message
...
In my code, I'm trying to specify a defined Name range. The specific
defined Name to be used is determined by the contents of C29.
I've tried the following which doesn't work.

res = Application.Match(cell.Value, Range(Range("C29").Value), 0)


If I use that exact range of the defined Name (as seen below), it
works
as
expected.
res = Application.Match(cell.Value, Range("L2:L50").Value, 0)

Does anyone know how I get get this statement to work accuratly when
referring to a defined Name range?

Thanks,
Paul