Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using names
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 -- hk ----------------------------------------------------------------------- hke's Profile: http://www.excelforum.com/member.php...nfo&userid=155 View this thread: http://www.excelforum.com/showthread.php?threadid=27411 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using names
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 "hke" wrote: Hi, I hope someone can help me out here. I`m having trouble using a defined 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 I think so don`t mind about that. To use the named range for the xvalues in my chart I tried the following: Set srs = Currentchart.SeriesCollection.NewSeries srs.XValues = ActiveWorkbook.Names.myX , but I get that "object doesn`t support property or merthod". Does anyone know the rigth way to assign myX to srs.XValues? -- hke ------------------------------------------------------------------------ hke's Profile: http://www.excelforum.com/member.php...fo&userid=1550 View this thread: http://www.excelforum.com/showthread...hreadid=274118 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using names
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. -- Regards, Tom Ogilvy "hke" wrote in message ... Hi, I hope someone can help me out here. I`m having trouble using a defined 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 I think so don`t mind about that. To use the named range for the xvalues in my chart I tried the following: Set srs = Currentchart.SeriesCollection.NewSeries srs.XValues = ActiveWorkbook.Names.myX , but I get that "object doesn`t support property or merthod". Does anyone know the rigth way to assign myX to srs.XValues? -- hke ------------------------------------------------------------------------ hke's Profile: http://www.excelforum.com/member.php...fo&userid=1550 View this thread: http://www.excelforum.com/showthread...hreadid=274118 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using names
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 "Tom Ogilvy" wrote : 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |