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

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

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

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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

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

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 InsertNameDefine, 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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

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

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.

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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 InsertNameDefine, 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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

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

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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 InsertNameDefine, 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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

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

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.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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 InsertNameDefine, 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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

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

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
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 InsertNameDefine,
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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