Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Application-Defined or Object-Defined Error on simple code | Excel Programming | |||
Application-Defined or Object-Defined Error on simple code | Excel Programming | |||
Application-defined or object-defined error on copy | Excel Programming | |||
Application-defined or object-defined error - missing the basics | Excel Programming |