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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

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" <[email protected]> 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" <[email protected]> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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
> >>> >>
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> 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" <[email protected]> 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >>> 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" <[email protected]> wrote in message
>> >>> news:[email protected]...
>> >>> > 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" <[email protected]> wrote in message
>> >>> > news:[email protected]...
>> >>> >> 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
>> >>> >>
>> >>> >>
>> >>> >>
>> >>> >
>> >>> >
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>