Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

Since I don't see any errorhandling - shouldn't he get a run time error when
try to reference an invalid named range?

Only other thing I could think of is the range references aren't fully
qualified maybe the active sheet is not the one that this macro is supposed
to run on - but, again, should get a run time error w/o any errorhandling.

"Tom Ogilvy" wrote:

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

Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

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

Defined Name Range (Application.Match)
 

I think I've discovered part of the problem, but I still haven't figured out
how to fix it. As stated below, by Named range consists of 3 columns
L2:N54. I found that if I reset that named range to one column (L2:L54),
then the code works properly. I put it back to original and the problem is
back. It is necessary for my Name ranges to consist of the three columns
that I've set. Does this clear up anything? Is there anything I can do
here? I really only need to compare the items in the first column of the
range. Maybe some type of Offset command.

Thanks,
Paul
"PCLIVE" wrote in message
news:REF5f.7168$v_5.5054@dukeread07...
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

Defined Name Range (Application.Match)
 

Just like in the worksheet, Match will only work with a single column or
single row.

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).Column(1), 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

Should work.

--
Regards,
Tom Ogilvy


"PCLIVE" wrote in message
news:qaG5f.7172$v_5.3101@dukeread07...
I think I've discovered part of the problem, but I still haven't figured
out
how to fix it. As stated below, by Named range consists of 3 columns
L2:N54. I found that if I reset that named range to one column (L2:L54),
then the code works properly. I put it back to original and the problem
is
back. It is necessary for my Name ranges to consist of the three columns
that I've set. Does this clear up anything? Is there anything I can do
here? I really only need to compare the items in the first column of the
range. Maybe some type of Offset command.

Thanks,
Paul
"PCLIVE" wrote in message
news:REF5f.7168$v_5.5054@dukeread07...
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

Defined Name Range (Application.Match)
 

Tom,

You are a genius with this stuff, as are many of the responders here. I
knew you would recognize the problem. There was a 'Compile error...Wrong
number of arguments or invalid property assignment' on "Column". I changed
it to "Columns" and it worked perfect.

Thanks a million for your help. I knew YOU would have the answer. Of
course you always did, I just wasn't giving you all of the questions.

Thanks again. I sincerely appreciate it.
Paul


"Tom Ogilvy" wrote in message
...
Just like in the worksheet, Match will only work with a single column or
single row.

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).Column(1), 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

Should work.

--
Regards,
Tom Ogilvy


"PCLIVE" wrote in message
news:qaG5f.7172$v_5.3101@dukeread07...
I think I've discovered part of the problem, but I still haven't figured
out
how to fix it. As stated below, by Named range consists of 3 columns
L2:N54. I found that if I reset that named range to one column (L2:L54),
then the code works properly. I put it back to original and the problem
is
back. It is necessary for my Name ranges to consist of the three columns
that I've set. Does this clear up anything? Is there anything I can do
here? I really only need to compare the items in the first column of the
range. Maybe some type of Offset command.

Thanks,
Paul
"PCLIVE" wrote in message
news:REF5f.7168$v_5.5054@dukeread07...
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