I pasted your code (with the name reference) and it worked fine for me w/o
any changes. Are you sure the value in C29 is the correct range name?


"PCLIVE" wrote:

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