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
--

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
--