Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
I have this.
Range("Green").Select "Green" is a define name of a cell range. If "Green" is also the contents of cell A2, how can I rewrite the Select statement so that it will refer to the defined name range that is in cell A2? Thanks, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
Range(Range("A2")).select works in XL2003
"PCLIVE" wrote: I have this. Range("Green").Select "Green" is a define name of a cell range. If "Green" is also the contents of cell A2, how can I rewrite the Select statement so that it will refer to the defined name range that is in cell A2? Thanks, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
one way:
Range(Range("A2").Value).Select another: ActiveWorkbook.Names(Range("A2").Value).RefersToRa nge.Select In article , "PCLIVE" wrote: I have this. Range("Green").Select "Green" is a define name of a cell range. If "Green" is also the contents of cell A2, how can I rewrite the Select statement so that it will refer to the defined name range that is in cell A2? Thanks, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
It appears to work in Excel 2000 as well.
Thank you. Paul "Toppers" wrote in message ... Range(Range("A2")).select works in XL2003 "PCLIVE" wrote: I have this. Range("Green").Select "Green" is a define name of a cell range. If "Green" is also the contents of cell A2, how can I rewrite the Select statement so that it will refer to the defined name range that is in cell A2? Thanks, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
JE,
I respect your advice as you have helped me many times before. I didn't try your second suggestion since the first one works and seems a bit simpler. However, I thought you might explain something to me. Is there any advantage or disadvantage between your suggested method and Toppers suggestion? Toppers' suggestion: Range(Range("A2")).select Your suggestion: Range(Range("A2").Value).Select There both very similar and seem to work. Does ".Value" give any added accuracy or stability, or is it just another way to write it. Thanks again for all of your help. Paul "JE McGimpsey" wrote in message ... one way: Range(Range("A2").Value).Select another: ActiveWorkbook.Names(Range("A2").Value).RefersToRa nge.Select In article , "PCLIVE" wrote: I have this. Range("Green").Select "Green" is a define name of a cell range. If "Green" is also the contents of cell A2, how can I rewrite the Select statement so that it will refer to the defined name range that is in cell A2? Thanks, Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
JE,
I've got one more thing that I need help with. This is still regarding defined name ranges. Selection.Sort Key1:=Range("AJ2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Im using the above code to sort. however, I need to base the 'SortKey1' on the first column of the selected unknown defined name range. Without knowing that AJ2 is the first column of the define name range that is selected (which was selected using the previous suggestion Range(Range("A2").Value).Select ), I need to sort based on the first column of the selected range Can this be done? Thanks again, Paul "JE McGimpsey" wrote in message ... one way: Range(Range("A2").Value).Select another: ActiveWorkbook.Names(Range("A2").Value).RefersToRa nge.Select In article , "PCLIVE" wrote: I have this. Range("Green").Select "Green" is a define name of a cell range. If "Green" is also the contents of cell A2, how can I rewrite the Select statement so that it will refer to the defined name range that is in cell A2? Thanks, Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
As far as VBA's concerned, there's no particular advantage or
disadvantage to either. Topper's suggestion is obviously shorter. I try to be explicit with the properties I reference. The .Value property is the default property for the Range object, so Dim a As Double a = Range("A2") and Dim a As Double a = Range("A2").Value are equivalent - the context implies that the .Value property is what is being referred to in the first example, not the object itself. I find that making the property explicit helps make the code more readable and maintainable. YMMV. In article , "PCLIVE" wrote: Is there any advantage or disadvantage between your suggested method and Toppers suggestion? Toppers' suggestion: Range(Range("A2")).select Your suggestion: Range(Range("A2").Value).Select There both very similar and seem to work. Does ".Value" give any added accuracy or stability, or is it just another way to write it. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
Thanks,
I agree with you. It gives a better sense of what was being targeted when the code was written. Thanks for the detailed explanation. "JE McGimpsey" wrote in message ... As far as VBA's concerned, there's no particular advantage or disadvantage to either. Topper's suggestion is obviously shorter. I try to be explicit with the properties I reference. The .Value property is the default property for the Range object, so Dim a As Double a = Range("A2") and Dim a As Double a = Range("A2").Value are equivalent - the context implies that the .Value property is what is being referred to in the first example, not the object itself. I find that making the property explicit helps make the code more readable and maintainable. YMMV. In article , "PCLIVE" wrote: Is there any advantage or disadvantage between your suggested method and Toppers suggestion? Toppers' suggestion: Range(Range("A2")).select Your suggestion: Range(Range("A2").Value).Select There both very similar and seem to work. Does ".Value" give any added accuracy or stability, or is it just another way to write it. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
First, are you using the defined name range or the Selection? There's no
need to select anything: With Range(Range("A2").Value) .Sort Key1:=.Cells(1,1), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With (and no, there's no reason to use continuation lines rather than putting it all on one line - I just find it more readable). In article , "PCLIVE" wrote: JE, I've got one more thing that I need help with. This is still regarding defined name ranges. Selection.Sort Key1:=Range("AJ2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Im using the above code to sort. however, I need to base the 'SortKey1' on the first column of the selected unknown defined name range. Without knowing that AJ2 is the first column of the define name range that is selected (which was selected using the previous suggestion Range(Range("A2").Value).Select ), I need to sort based on the first column of the selected range Can this be done? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting a defined Name via a cell's contents
I am using a defined name range. And you are correct as usual. Your code
is much prettier than what I was using. I have updated my code to mirror your suggestions. It works great! Thanks for everything. Paul "JE McGimpsey" wrote in message ... First, are you using the defined name range or the Selection? There's no need to select anything: With Range(Range("A2").Value) .Sort Key1:=.Cells(1,1), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With (and no, there's no reason to use continuation lines rather than putting it all on one line - I just find it more readable). In article , "PCLIVE" wrote: JE, I've got one more thing that I need help with. This is still regarding defined name ranges. Selection.Sort Key1:=Range("AJ2"), Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Im using the above code to sort. however, I need to base the 'SortKey1' on the first column of the selected unknown defined name range. Without knowing that AJ2 is the first column of the define name range that is selected (which was selected using the previous suggestion Range(Range("A2").Value).Select ), I need to sort based on the first column of the selected range Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cell's contents as an index to a row ... ? | Excel Worksheet Functions | |||
Selecting a cell depending on the other cell's value, kind of | Links and Linking in Excel | |||
link to a cell's contents | Excel Worksheet Functions | |||
link to a cell's contents | Excel Worksheet Functions | |||
link to a cell's contents | Excel Worksheet Functions |