Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a named range
My data spreadsheet has several named ranges, with meaningful names. I'd like
to refer to them in VBA, but all attempts so far lead me to absolute addresses like $R1$C1, or sheet1!R23C45. If a user cselects a named range how can I read the name of the range (that might be a sinlge cell) and how can I write to another named range (that will have many cells)? Many thanks whoever is out there. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a named range
If the the selection is the entire named range and nothing else
msgbox selection.name.name if the user selects A1:B2 and the named range is only A1, then selection.Name.Name will return an error. Other than that, you would have to loop through the names collection and test where the selection is relative to each named range. You can refer to a known named range with set rng = Range("MyNamedRange") selection.Copy Destination:=rng -- Regards, Tom Ogilvy "John M." wrote in message ... My data spreadsheet has several named ranges, with meaningful names. I'd like to refer to them in VBA, but all attempts so far lead me to absolute addresses like $R1$C1, or sheet1!R23C45. If a user cselects a named range how can I read the name of the range (that might be a sinlge cell) and how can I write to another named range (that will have many cells)? Many thanks whoever is out there. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference a named range
Great, thanks Tom. "name.name" is one of the less intuitive versions that I
did not try. I can pre-filter all multiple selections and get exactly what I need. Best wishes "Tom Ogilvy" wrote: If the the selection is the entire named range and nothing else msgbox selection.name.name if the user selects A1:B2 and the named range is only A1, then selection.Name.Name will return an error. Other than that, you would have to loop through the names collection and test where the selection is relative to each named range. You can refer to a known named range with set rng = Range("MyNamedRange") selection.Copy Destination:=rng -- Regards, Tom Ogilvy "John M." wrote in message ... My data spreadsheet has several named ranges, with meaningful names. I'd like to refer to them in VBA, but all attempts so far lead me to absolute addresses like $R1$C1, or sheet1!R23C45. If a user cselects a named range how can I read the name of the range (that might be a sinlge cell) and how can I write to another named range (that will have many cells)? Many thanks whoever is out there. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference first cell in a named range | Excel Worksheet Functions | |||
Simple: how to express this named-range reference? | Excel Discussion (Misc queries) | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Named Range reference via single Cell | Excel Discussion (Misc queries) | |||
named range, offset self-reference | Excel Discussion (Misc queries) |