View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
IanC[_2_] IanC[_2_] is offline
external usenet poster
 
Posts: 157
Default "Set" statement with variables?

Is it possible to use variables in a "Set" statement?

For example, I have the following lines:

With Worksheets("Lookup")
Set General_kV = ("Lookup").Range("General_kV")
Set General_mA = .Range("General_mA")
Set General_Timer = .Range("General_Timer")
(...etc. approx 90 other Set statements).
End With

I can recover the named ranges from the Lookup worksheet but I don't know
how (or if) it's possible to apply them to a variable in the Set statement.

What I'm hoping for is something along the lines of the following code but I
need both instances of X to be substituted with General_kV, General_mA,
General_Timer, etc.

Sub SetRanges()
Set nms = ThisWorkbook.Names
For r = 1 To nms.Count
X = nms(r).Name
If Left(nms(r).RefersTo, 8) = "=Lookup!" Then
Set X = Worksheets("Lookup").Range(X)
End If
Next
Set nms = Nothing
End Sub


Any ideas?

--
Ian
--