Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object ?
Hi there I am wondering if someone can tell me how I can return the XValues
of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object ?
IIRC, I've had to pull out the formula information and parse out the XValues.
"Dan Thompson" wrote: Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object ?
I think the help file lies. It says that the XValues is a read/write value.
I have tried several different combinations of the chartobject.Chart.SeriesCollection, etc. and the best I got was wrong type. Have you tried cross posting to the Charts group. Maybe some guru over there has an answer. "Barb Reinhardt" wrote in message ... IIRC, I've had to pull out the formula information and parse out the XValues. "Dan Thompson" wrote: Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object ?
I virtually gave you a solution a few days ago, briefly
arr = Split(Mid$(sFmla, 9, Len(sFmla) - 9), ",") on error resume next set rngX = arr(1) ' XValues set rngY = arr(2) ' YValues MyString = rngX.address (you might want to include external:=true) Regards, Peter T PS, I also sent you an email off-line "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object ?
I got this from the chart group archives:
Sub test2() Dim arr As Variant Dim i As Integer With ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1) arr = .XValues For i = LBound(arr) To UBound(arr) Debug.Print arr(i) Next End With End Sub It returns the values, not the address. "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object
Yes Peter I did get your email thanks
And I know that you gave me a solution for returning chart series formula and using the split function to parse it and extract the table name however this is a different question I am not interested in the data series of the chart this time nor am I interested in the worksheet name I am interested in the XValue the one that returns all the XAxis values that apear on the bottom XAxis of the chart. Basicly I have a chart where the X-Axis Values are dates and I want to return the formual in $A1:$A2 style string because I already have another parsing function that will get the column letter from that style / format of returned string. Problem is that when you try to have VBA return the Xvalues property of a chart it only seems to return it in R1C1 format ? My String parsing function is designed to strip out the column LETTER only of a returned formlua string Dan "Peter T" wrote: I virtually gave you a solution a few days ago, briefly arr = Split(Mid$(sFmla, 9, Len(sFmla) - 9), ",") on error resume next set rngX = arr(1) ' XValues set rngY = arr(2) ' YValues MyString = rngX.address (you might want to include external:=true) Regards, Peter T PS, I also sent you an email off-line "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object
I don't follow what you want / don't want.
"I am not interested in the data series of the chart this time" and "I am interested in the XValue the one that returns all the XAxis values" These are mutually exclusive statements, the XValues are part of the chart data. The code I proposed showed one way you can return the source the XValues as a range object of from which you can return the address in A1 or R1C1 style. Depending on how you defined the source there might be no XValues linked to cells and the XValues will automatically be applied, indeed that's quite typical. But in that case clearly you cannot return a range address for the XValues. FWIW you can convert A1 < R1C1 with application.ConvertFormula. Be careful with R1C1 as you'll need to convert everything relative to the activecell. But you don't need to go down that route. Regards, Peter T "Dan Thompson" wrote in message ... Yes Peter I did get your email thanks And I know that you gave me a solution for returning chart series formula and using the split function to parse it and extract the table name however this is a different question I am not interested in the data series of the chart this time nor am I interested in the worksheet name I am interested in the XValue the one that returns all the XAxis values that apear on the bottom XAxis of the chart. Basicly I have a chart where the X-Axis Values are dates and I want to return the formual in $A1:$A2 style string because I already have another parsing function that will get the column letter from that style / format of returned string. Problem is that when you try to have VBA return the Xvalues property of a chart it only seems to return it in R1C1 format ? My String parsing function is designed to strip out the column LETTER only of a returned formlua string Dan "Peter T" wrote: I virtually gave you a solution a few days ago, briefly arr = Split(Mid$(sFmla, 9, Len(sFmla) - 9), ",") on error resume next set rngX = arr(1) ' XValues set rngY = arr(2) ' YValues MyString = rngX.address (you might want to include external:=true) Regards, Peter T PS, I also sent you an email off-line "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object ?
It is read/write. When you write you can use a range address or object or an
array. When you read you only get the array. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "JLGWhiz" wrote in message ... I think the help file lies. It says that the XValues is a read/write value. I have tried several different combinations of the chartobject.Chart.SeriesCollection, etc. and the best I got was wrong type. Have you tried cross posting to the Charts group. Maybe some guru over there has an answer. "Barb Reinhardt" wrote in message ... IIRC, I've had to pull out the formula information and parse out the XValues. "Dan Thompson" wrote: Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object
It seems that you want the address in $A$1 format? What Peter offered you,
and you've rejected, is the address in Sheet1!$A$1 format. Parse out the sheet name, and you've got exactly what you want. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Dan Thompson" wrote in message ... Yes Peter I did get your email thanks And I know that you gave me a solution for returning chart series formula and using the split function to parse it and extract the table name however this is a different question I am not interested in the data series of the chart this time nor am I interested in the worksheet name I am interested in the XValue the one that returns all the XAxis values that apear on the bottom XAxis of the chart. Basicly I have a chart where the X-Axis Values are dates and I want to return the formual in $A1:$A2 style string because I already have another parsing function that will get the column letter from that style / format of returned string. Problem is that when you try to have VBA return the Xvalues property of a chart it only seems to return it in R1C1 format ? My String parsing function is designed to strip out the column LETTER only of a returned formlua string Dan "Peter T" wrote: I virtually gave you a solution a few days ago, briefly arr = Split(Mid$(sFmla, 9, Len(sFmla) - 9), ",") on error resume next set rngX = arr(1) ' XValues set rngY = arr(2) ' YValues MyString = rngX.address (you might want to include external:=true) Regards, Peter T PS, I also sent you an email off-line "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object
Sorry Peter for the confusion I did not explain my question very clearly
I reposted my question more clearly in the charts group and it was answered by Andy here is the link.... http://www.microsoft.com/communities...=en-us&m=1&p=1 Also here is a link that Andy gave me explaining why what I was trying to do was failing it has to do with Excel's Object Model having a serious flaw http://spreadsheetpage.com/index.php..._chart_series/ Dan Thompson "Peter T" wrote: I don't follow what you want / don't want. "I am not interested in the data series of the chart this time" and "I am interested in the XValue the one that returns all the XAxis values" These are mutually exclusive statements, the XValues are part of the chart data. The code I proposed showed one way you can return the source the XValues as a range object of from which you can return the address in A1 or R1C1 style. Depending on how you defined the source there might be no XValues linked to cells and the XValues will automatically be applied, indeed that's quite typical. But in that case clearly you cannot return a range address for the XValues. FWIW you can convert A1 < R1C1 with application.ConvertFormula. Be careful with R1C1 as you'll need to convert everything relative to the activecell. But you don't need to go down that route. Regards, Peter T "Dan Thompson" wrote in message ... Yes Peter I did get your email thanks And I know that you gave me a solution for returning chart series formula and using the split function to parse it and extract the table name however this is a different question I am not interested in the data series of the chart this time nor am I interested in the worksheet name I am interested in the XValue the one that returns all the XAxis values that apear on the bottom XAxis of the chart. Basicly I have a chart where the X-Axis Values are dates and I want to return the formual in $A1:$A2 style string because I already have another parsing function that will get the column letter from that style / format of returned string. Problem is that when you try to have VBA return the Xvalues property of a chart it only seems to return it in R1C1 format ? My String parsing function is designed to strip out the column LETTER only of a returned formlua string Dan "Peter T" wrote: I virtually gave you a solution a few days ago, briefly arr = Split(Mid$(sFmla, 9, Len(sFmla) - 9), ",") on error resume next set rngX = arr(1) ' XValues set rngY = arr(2) ' YValues MyString = rngX.address (you might want to include external:=true) Regards, Peter T PS, I also sent you an email off-line "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I return the XValues of the Series collection object
Sorry confusion I did not explain my question very clearly
I reposted my question more clearly in the charts group and it was answered by Andy here is the link.... http://www.microsoft.com/communities...=en-us&m=1&p=1 Also here is a link that Andy gave me explaining why what I was trying to do was failing it has to do with Excel's Object Model having a serious flaw http://spreadsheetpage.com/index.php..._chart_series/ "Jon Peltier" wrote: It seems that you want the address in $A$1 format? What Peter offered you, and you've rejected, is the address in Sheet1!$A$1 format. Parse out the sheet name, and you've got exactly what you want. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Dan Thompson" wrote in message ... Yes Peter I did get your email thanks And I know that you gave me a solution for returning chart series formula and using the split function to parse it and extract the table name however this is a different question I am not interested in the data series of the chart this time nor am I interested in the worksheet name I am interested in the XValue the one that returns all the XAxis values that apear on the bottom XAxis of the chart. Basicly I have a chart where the X-Axis Values are dates and I want to return the formual in $A1:$A2 style string because I already have another parsing function that will get the column letter from that style / format of returned string. Problem is that when you try to have VBA return the Xvalues property of a chart it only seems to return it in R1C1 format ? My String parsing function is designed to strip out the column LETTER only of a returned formlua string Dan "Peter T" wrote: I virtually gave you a solution a few days ago, briefly arr = Split(Mid$(sFmla, 9, Len(sFmla) - 9), ",") on error resume next set rngX = arr(1) ' XValues set rngY = arr(2) ' YValues MyString = rngX.address (you might want to include external:=true) Regards, Peter T PS, I also sent you an email off-line "Dan Thompson" wrote in message ... Hi there I am wondering if someone can tell me how I can return the XValues of the Sieriescollection object. But I want them to be returned in $A1:$A10 style not R1C1 style. just need to return them to a string variable like Somthing like MyString = Activechart.Seriescollection(1).XValues I have tried this and it doesn't work. Any Ideas ?? Dan Thompson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to Set the XValues Property of the Series | Charts and Charting in Excel | |||
Series XValues, determine if labels or values | Excel Programming | |||
Unable to set the XValues property of the Series class | Charts and Charting in Excel | |||
Function unable to return Collection object | Excel Programming |