Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reducing "Set" statements
I have multiple named ranges which are referred to in code. To use these
ranges I use a series of "Set" statements near the start of my code. eg With Worksheets("Lookup") Set GeneralTubeCount = .Range("GeneralTubeCount") Set GeneralkVmAs = .Range("GeneralkVmAs") Set GeneralField = .Range("GeneralField") Set GeneralFieldVB = .Range("GeneralFieldVB") etc etc etc End With Is there a way to reduce the code down given that the "Set" and "Range" names are always the same? I've tried the following code but it fails with "Run-time error 438 - Object doesn't support this property or method" Sub test() Set nms = ActiveWorkbook.Names With Worksheets("Lookup") For r = 1 To nms.Count Set nms(r).Name = .Range(nms(r).Count) 'crashes on this line Next End With End Sub Any ideas? -- Ian -- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reducing "Set" statements
Sorry. Mistake in my code, but still the same result.
Sub test() Set nms = ActiveWorkbook.Names With Worksheets("Lookup") For r = 1 To nms.Count Set nms(r).Name = .Range(nms(r).Name) 'crashes on this line Next End With End Sub -- Ian -- "IanC" wrote in message news:Kob9o.38$qQ6.10@hurricane... I have multiple named ranges which are referred to in code. To use these ranges I use a series of "Set" statements near the start of my code. eg With Worksheets("Lookup") Set GeneralTubeCount = .Range("GeneralTubeCount") Set GeneralkVmAs = .Range("GeneralkVmAs") Set GeneralField = .Range("GeneralField") Set GeneralFieldVB = .Range("GeneralFieldVB") etc etc etc End With Is there a way to reduce the code down given that the "Set" and "Range" names are always the same? I've tried the following code but it fails with "Run-time error 438 - Object doesn't support this property or method" Sub test() Set nms = ActiveWorkbook.Names With Worksheets("Lookup") For r = 1 To nms.Count Set nms(r).Name = .Range(nms(r).Count) 'crashes on this line Next End With End Sub Any ideas? -- Ian -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Need more than 7 nested "IF" statements in Excel" | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |