Hi Garry
About your 2 points below.
1. I wasn't aware there is such a distinction as workbook level names &
sheet-level names. How do you determine which they are and change between
them?
I use Insert Name Define... to create the names. There isn't an option
to specify workbook or worksheet names.
2. I appreciate the pointer that I don't need to use a variable, but if the
names are localised to the sheet would I need to add this line?
If Left$(n.RefersTo, 8) = "=Lookup!" Then
I only put that in because there are 3 names referring to an external
workbook and needed to work only with the Lookup sheet names.
Where you added the line
'do stuff
the stuff I need to do is the equivalent of
Set X = Worksheets("Lookup").Range(X)
I suppose in your code this would be
Set n = Worksheets("Lookup").Range(n)
This is really the problem I'm having. All the rest is interesting, but not
essential.
--
Ian
--
"GS" wrote in message
...
Jim Cone wrote :
Why do you need 90 + Set statements?
If you already have named ranges why not use them...
If Worksheets("Lookup").Range("Sludge").Cells(2, 1).Value 100 Then
'do something
End If
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/XLCompanion
.
.
.
"IanC" wrote in message
news:mxxao.84806$y_2.69065@hurricane...
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
Couple things occur to me after reading your approach...
1. I'm not sure why you're using workbook level names for ranges on a
specific sheet, but I recommend you change them to sheet-level names so
they're exclusive to the sheet.
2. Once the defined name ranges are localized to Sheets("Lookup"), your
For...Next loop seems a reasonable approach on its own, but I'd ditch
loading the names into a variable since your loop can work directly on
them without the added overhead. Example...
Dim n as Object, wksSource As Worksheet
Set wksSource = ThisWorkbook.Sheets("Lookup")'just for clarity
For Each n in wksSource.Names
If Left$(n.RefersTo, 8) = "=Lookup!" Then
With wksSource.Range(n)
'do stuff
End With
End If
Next n
Also, if you have a specific list of named ranges that you want to work
with then you could store them into a delimited string and use the InStr()
function to validate that n is one you want, OR you could just iterate the
string var in a loop and only operate on the desired named ranges in your
list.
HTH
--
Garry
Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc