Named Range not appearing in names drop down

[michael.beckinsale]

Hi All,

Sorry if posted to 2 newsgroups - my mistake.

The code snippet below has been used to add a named range and this
appears via Insert, Define, Names and can be used for calculations
etc. The problem is if l use the drop down name box it is not there!
can anybodt throw some light on this please. I need the name to appear
in the dropdown


ActiveWorkbook.Names.add Name:=RngName, RefersTo:= _
"=OFFSET(" & sht & tlc & ",0,0," & Rfun & "," & Cfun & ")"

Regards

Michael

 

[Bob Phillips]

Dynamic range names do appear in the dropown. The reason is presumably
because selecting an item from that list goes to that range, and it is not
determined at that point where the range points to, that info is updated
when the range name is created/updated, so it doesn't know where a dynamic
range name points at.

 

[Nigel]

Have you assigned a name to the variable RngName ?

Also does the RefersTo resolve to a valid range?

 

[michael.beckinsale]

Hi Bob,

Thanks for respondind so quickly, l think you had a typo, did you mean
dynamic ranges DO NOT appear in the dropdown?

Regards

Michael

 

[Charles Williams]

your Name refers to a formula rather than just a Range reference, and Named
Formulae do not appear in the Names Box dropdown.
(they do appear when using Name Manager -
http://www.decisionmodels.com/downloads.htm )

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Bob Phillips]

Indeed it was, indeed I did.