(Using XL2000) I selected a range and used InsertNameDefine to add the
name. It shows up in the Name box in the top left, and can be selected from
there.

I was trying to select the range using
For Each nm In wkb.Names
If Left(nm, 4) = "Calc" Then
Set rng = Range(nm)
End If

but it kept coming up with an error. So I inserted a Debug.Print for every
name, and the name was not there!

Two questions, then:
(1) How do I set the name of a range so my code will see it?
(2) Since the Name belongs to the Book object, but the Range belongs to the
Sheet object, am I setting the range properly?

Ed

If you made your name a worksheet level name (i.e., included the sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.

Note that you can use the .RefersToRange property once you find the
name, rather than resolving it with the Range() method:

Set rng = nm.RefersToRange




In article ,
"Ed" wrote:

(Using XL2000) I selected a range and used InsertNameDefine to add the
name. It shows up in the Name box in the top left, and can be selected from
there.

I was trying to select the range using
For Each nm In wkb.Names
If Left(nm, 4) = "Calc" Then
Set rng = Range(nm)
End If

but it kept coming up with an error. So I inserted a Debug.Print for every
name, and the name was not there!

Two questions, then:
(1) How do I set the name of a range so my code will see it?
(2) Since the Name belongs to the Book object, but the Range belongs to the
Sheet object, am I setting the range properly?

Ed

also, in a general module you can refer to it directly

msgbox range("MyName").Address

In a sheet module, unless the range is in that sheet, you need to preface it
with the sheet reference

msgbox Worksheets("Sheet2").Range("MyName").Address

an easy way to create a named range is

Worksheets("Sheet3").Range("C1:G20").Name = "MyName1"

msgbox range("MyName1").Address(0,0,,True)



--
Regards,
Tom Ogilvy

"JE McGimpsey" wrote in message
...
If you made your name a worksheet level name (i.e., included the sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.

Note that you can use the .RefersToRange property once you find the
name, rather than resolving it with the Range() method:

Set rng = nm.RefersToRange




In article ,
"Ed" wrote:

(Using XL2000) I selected a range and used InsertNameDefine to add
the
name. It shows up in the Name box in the top left, and can be selected
from
there.

I was trying to select the range using
For Each nm In wkb.Names
If Left(nm, 4) = "Calc" Then
Set rng = Range(nm)
End If

but it kept coming up with an error. So I inserted a Debug.Print for
every
name, and the name was not there!

Two questions, then:
(1) How do I set the name of a range so my code will see it?
(2) Since the Name belongs to the Book object, but the Range belongs to
the
Sheet object, am I setting the range properly?

Ed

Thank you. I had forgotten about RefersToRange. I still don't understand,
though, why
For Each nm in wkb.Names
Debug.Print nm
Next nm
would not show the Defined Name. Any suggestions?

Ed

"JE McGimpsey" wrote in message
...
If you made your name a worksheet level name (i.e., included the sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.

Note that you can use the .RefersToRange property once you find the
name, rather than resolving it with the Range() method:

Set rng = nm.RefersToRange




In article ,
"Ed" wrote:

(Using XL2000) I selected a range and used InsertNameDefine to add
the
name. It shows up in the Name box in the top left, and can be selected
from
there.

I was trying to select the range using
For Each nm In wkb.Names
If Left(nm, 4) = "Calc" Then
Set rng = Range(nm)
End If

but it kept coming up with an error. So I inserted a Debug.Print for
every
name, and the name was not there!

Two questions, then:
(1) How do I set the name of a range so my code will see it?
(2) Since the Name belongs to the Book object, but the Range belongs to
the
Sheet object, am I setting the range properly?

Ed

Are you sure you're accessing the right workbook?

Do you see the name in the Insert/Names/Define dialog?



In article ,
"Ed" wrote:

Thank you. I had forgotten about RefersToRange. I still don't understand,
though, why
For Each nm in wkb.Names
Debug.Print nm
Next nm
would not show the Defined Name. Any suggestions?

Ed

"JE McGimpsey" wrote in message
...
If you made your name a worksheet level name (i.e., included the sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.

It's the only workbook open. Yes, the names are there.

"JE McGimpsey" wrote in message
...
Are you sure you're accessing the right workbook?

Do you see the name in the Insert/Names/Define dialog?



In article ,
"Ed" wrote:

Thank you. I had forgotten about RefersToRange. I still don't
understand,
though, why
For Each nm in wkb.Names
Debug.Print nm
Next nm
would not show the Defined Name. Any suggestions?

Ed

"JE McGimpsey" wrote in message
...
If you made your name a worksheet level name (i.e., included the sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define
will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.

The default property for a name object is the refers to:

? thisworkbook.Names("Howdy")
=Sheet3!$S$1:$T$7

You code says

debug.print nm

perhaps you are not recognizing it. Try using

debug.print nm.Name, nm.RefersTo

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
It's the only workbook open. Yes, the names are there.

"JE McGimpsey" wrote in message
...
Are you sure you're accessing the right workbook?

Do you see the name in the Insert/Names/Define dialog?



In article ,
"Ed" wrote:

Thank you. I had forgotten about RefersToRange. I still don't
understand,
though, why
For Each nm in wkb.Names
Debug.Print nm
Next nm
would not show the Defined Name. Any suggestions?

Ed

"JE McGimpsey" wrote in message
...
If you made your name a worksheet level name (i.e., included the
sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define
will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.

Bingo! I was trying to use the object itself, rather than a property of the
object. Thanks, Tom.

"Tom Ogilvy" wrote in message
...
The default property for a name object is the refers to:

? thisworkbook.Names("Howdy")
=Sheet3!$S$1:$T$7

You code says

debug.print nm

perhaps you are not recognizing it. Try using

debug.print nm.Name, nm.RefersTo

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
It's the only workbook open. Yes, the names are there.

"JE McGimpsey" wrote in message
...
Are you sure you're accessing the right workbook?

Do you see the name in the Insert/Names/Define dialog?



In article ,
"Ed" wrote:

Thank you. I had forgotten about RefersToRange. I still don't
understand,
though, why
For Each nm in wkb.Names
Debug.Print nm
Next nm
would not show the Defined Name. Any suggestions?

Ed

"JE McGimpsey" wrote in message
...
If you made your name a worksheet level name (i.e., included the
sheet
name in the "Name in workbook" field:

Sheet1!MyName

(only "MyName" will appear in the Name box, but Insert/Name/Define
will
show the worksheet in the list) then the name contains the sheet
reference and your Left(nm,4) won't pick it up.