using names

[hke]

Hi,

I hope someone can help me out here. I`m having trouble using a define
dynamic range that I added as a name like this:

ActiveWorkbook.Names.Add Name:="myX", RefersToR1C1:= _
"=""FORSKYVNING(sheet1!$G$10;0;0;1;sheet2!$B$7)"""

The word FORSKYVNING is just the norwegian translation of OFFSET
think so don`t mind about that.

To use the named range for the xvalues in my chart I tried th
following:

Set srs = Currentchart.SeriesCollection.NewSeries
srs.XValues = ActiveWorkbook.Names.myX

, but I get that "object doesn`t support property or merthod". Doe
anyone know the rigth way to assign myX to srs.XValues

 

[Guest]

Hi
not tested but try:
ActiveWorkbook.Names.Add Name:="myX", RefersToR1C1:= _
"=OFFSET(sheet1!$G$10,0,0,1,sheet2!$B$7)"

Don't use the local´function names. Though there's a referstoR1C1local
property don't use it. It is (at least in my experience) VERY buggy. Also you
may always use the international function names as otherwise your code
wouldn't run in a non-Norwegian version

 

[Tom Ogilvy]

I would worry about FORSKYVNING as I don't think ReferstoR1C1 will accept
it. (also, you use ReferstoR1C1 and provide A1 type addressing as an
argument.)

I would turn on the macro recorder and create the name manually using that
formula, then see what Excel records. I suspect it will record using
OFFSET. In any event, it won't record the double double quotes (if you do
things properly) which would make your formula nothing but a string and
therefore meaningless in the context you want to use it.

You need to get the defined name set up properly. Then you can again use
the macro recorder while you set the xvalues to that name manually.

I recorded it and got:
ActiveWorkbook.Names.Add Name:="MyX", RefersToR1C1:= _
"=OFFSET(Sheet1!R10C7,0,0,1,Sheet2!R7C2)"


Which worked.

If you want to use your local formula you need to use RefersToR1C1Local but
you still need to get rid of A1 addressing and the double double quotes.

 

[keepITcool]

NOTE:

DO NOT use RefersToR1C1Local when adding names..

somebody was sleeping when they programmed that method.

you'll need to use USenglish separators (decimal, list, and for arrays:
row and column) and USenglish R1C1 cell references with [] brackets, but
LOCAL function names..

(I've a few conversion functions: 250 lines of code...)


argh!

my advice when adding or modifying Names thru code:

stick with ENGLISH
stick with R1C1



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >