Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy named range based on variable
Is it possible to copy a named range based on a variable?
I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently contain 2.15 or 2.16. This line: sMTSVer = "MTS" & Worksheets("Input").Range("T3") * 100 & "Vision30" makes sMTSVer = MTS216Vision30 This works: MTS216Vision30.Copy .Range("A5") But this doesn't: sMTSVer.Copy .Range("A5") I know I could use something like ..... With Worksheets("Input") If .Range("T3")=2.15 then MTS215Vision30.Copy .Range("A5") Elseif .Range("T3")=2.16 then MTS216Vision30.Copy .Range("A5") End If End WIth ...... but in time there will be 2.17, 2.18 etc and I am hoping to "future proof" the code so that I only need to create the named ranges and add a "Set" line rather than having to add more If conditions, as the Vision30 example is only one of a number of similar situations. Any ideas? I need this to work on any machine with a "default" Excel 2000 installation (ie not an add-in). -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy named range based on variable
If you have a string that names a range, just use Range to get a real range from the string. E.g., sMTSVer = "some string" Set R = Range(sMTSVer) R.Copy destiantion:=Range("A5") Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 10 Dec 2009 19:37:29 -0000, "IanC" wrote: Is it possible to copy a named range based on a variable? I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently contain 2.15 or 2.16. This line: sMTSVer = "MTS" & Worksheets("Input").Range("T3") * 100 & "Vision30" makes sMTSVer = MTS216Vision30 This works: MTS216Vision30.Copy .Range("A5") But this doesn't: sMTSVer.Copy .Range("A5") I know I could use something like ..... With Worksheets("Input") If .Range("T3")=2.15 then MTS215Vision30.Copy .Range("A5") Elseif .Range("T3")=2.16 then MTS216Vision30.Copy .Range("A5") End If End WIth ..... but in time there will be 2.17, 2.18 etc and I am hoping to "future proof" the code so that I only need to create the named ranges and add a "Set" line rather than having to add more If conditions, as the Vision30 example is only one of a number of similar situations. Any ideas? I need this to work on any machine with a "default" Excel 2000 installation (ie not an add-in). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy named range based on variable
Thanks Chip. It wasn't quite the way I'd intended, but with a bit of
recoding it's working well. -- Ian -- "Chip Pearson" wrote in message ... If you have a string that names a range, just use Range to get a real range from the string. E.g., sMTSVer = "some string" Set R = Range(sMTSVer) R.Copy destiantion:=Range("A5") Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Thu, 10 Dec 2009 19:37:29 -0000, "IanC" wrote: Is it possible to copy a named range based on a variable? I have a number of named ranges which I need to copy based on the results of a cell value. My whole routine is nearly 500 lines of code, so I've only included the relevant bits in this message. For example, I have the following lines at the start of my routine: With Worksheets("Lookup") Set MTS215Vision30 = .Range("MTS2.16Vision30") Set MTS216Vision30 = .Range("MTS2.16Vision30") End With and want to copy one of these based on the value of cell which could currently contain 2.15 or 2.16. This line: sMTSVer = "MTS" & Worksheets("Input").Range("T3") * 100 & "Vision30" makes sMTSVer = MTS216Vision30 This works: MTS216Vision30.Copy .Range("A5") But this doesn't: sMTSVer.Copy .Range("A5") I know I could use something like ..... With Worksheets("Input") If .Range("T3")=2.15 then MTS215Vision30.Copy .Range("A5") Elseif .Range("T3")=2.16 then MTS216Vision30.Copy .Range("A5") End If End WIth ..... but in time there will be 2.17, 2.18 etc and I am hoping to "future proof" the code so that I only need to create the named ranges and add a "Set" line rather than having to add more If conditions, as the Vision30 example is only one of a number of similar situations. Any ideas? I need this to work on any machine with a "default" Excel 2000 installation (ie not an add-in). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy&Paste variable range based on cell value | Excel Programming | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Finding a named range based on cell value and copy/paste to same sheet? | Excel Programming | |||
referencing a named range using a variable | Excel Programming | |||
Can A Named Range Be A Variable? | Excel Programming |