Can't get "handle" on named range??!?

[Ed]

I am trying to loop through the ranges in my workbook and set a string to
the values of the first two cells in each range. I can access the Names
collection and get the name as Sheet and Cell identifiers
(Sheet1!$A$1:$A$4), and I can use the .Name property to get the name I gave
the range. But I can't seem to get the range itself to get the values from
the Cells(1,1) and Cells(1,2).

Ed

Set wb = ActiveWorkbook

On Error Resume Next
For Each rngName In wb.Names
rngName.RefersToRange.Select
strRng = rngName.Name
rngWork = ActiveWorkbook.Range(rngName.Name)
strOptn = rngWork.Cells(1, 1).Value
strOptn = strOptn & " Size " & rngWork.Cells(1, 2).Value
MsgBox strOptn
Next rngName
On Error GoTo 0

 

[Tom Ogilvy]

Set wb = ActiveWorkbook

On Error Resume Next
For Each rngName In wb.Names
strRng = rngName.Name
set rngWork = rngName.RefersToRange
strOptn = rngWork.Cells(1, 1).Value
strOptn = strOptn & " Size " & rngWork.Cells(1, 2).Value
MsgBox strOptn
Next rngName
On Error GoTo 0

 

[Ed]

Tom, the MsgBox strOptn comes up blank! Also, if I comment out On Error
Resume Next, I get an error 1004 - Application-defined or object defined
error - on

set rngWork = rngName.RefersToRange

Is it perhaps something in how I'm setting the ranges? I build a string for
the name and a string for the address and use:
wb.Names.Add _
Name:=strRng, _
RefersToR1C1:=strAddr
I notice these names can be accessed through Insert>>Name>>Define, but not
in the name box to the left of the formula bar. Did I leave something out
that's killing this?

Ed

 

[Tom Ogilvy]

Go back into Insert=>Names=>Define

look at one of your names. It should look something like

=Sheet1!$A$1:$A$10

not
="Sheet1!$A$1:$A$10"

if strAddr is not in R1C1 format, don't use ReferToR1C1
Assume it isn't, so it would be:
wb.Names.Add _
Name:=strRng, _
RefersTo:="=" & strAddr

Notice the additional "="

Another way to name a range is (and simpler)

worksheets("Sheet1").Range("A1:A10").Name = "myrange1"

or

i = 1
for each cell in Range("A1:A10")
cell.Name = "myrange" & i
Next

if you wanted individual names as an example.

 

[Ed]

I found it, Tom. My names did look like
=Sheet1!$A$1:$A$10
I manually added a new name through the dialog box and looked at the name.
It was
='Sheet1'!$A$1:$A$10
with the SheetName bracketed in ' ' . I added that to building strAddr
strAddr = "=" & "'" & ws.Name & "'" & _
"!R" & i & "C1:R" & i & "C6"
and now it works fine. The names show up in the box and I can "handle" the
range.

Thanks for the boost.
Ed

 

[Tom Ogilvy]

That may very well be the problem, but just for info, you don't need single
quotes unless the worksheet name has a space in it.

 

[Ed]

That was indeed the issue! *sigh* It's always those simple things . . .