How Can I return the XValues of the Series collection object ?

[Dan Thompson]

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

 

[Barb Reinhardt]

IIRC, I've had to pull out the formula information and parse out the XValues.

 

[JLGWhiz]

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.

 

[Peter T]

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

 

[JLGWhiz]

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]

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]

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

 

[Jon Peltier]

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]

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-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Dan Thompson]

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/communitie...d3122b786559&lang=en&cr=US&sloc=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/tip/a_class_module_to_manipulate_a_chart_series/

Dan Thompson

 

[Dan Thompson]

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/communitie...d3122b786559&lang=en&cr=US&sloc=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/tip/a_class_module_to_manipulate_a_chart_series/