Putting SeriesCollection Value into String

specs

New Member
Joined
Oct 26, 2005
Messages
27
Can anyone help me out here?

Code:
Dim s1 As String
s1 = ActiveChart.SeriesCollection(1).XValues.Text
or
Code:
Dim s1 As String
s1 = ActiveChart.SeriesCollection(1).XValues.Value
i tried .text and .value gives me a object required error.
Is there any ways i can put the series value into my string(s1)?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The XValues property returns a variant array:

Code:
Dim s1 As Variant
s1 = ActiveChart.SeriesCollection(1).XValues
 

specs

New Member
Joined
Oct 26, 2005
Messages
27
Thanks for the answer andrew. But i got another problem.

what should i do if i want to add a "Sheet1!R" & row+1 to the .XValues array?
 

specs

New Member
Joined
Oct 26, 2005
Messages
27
An example of my question is:

The original values is below, checked from the macro recorder:
ActiveChart.SeriesCollection(1).Values = "=(Sheet1!R1,Sheet1!R2,Sheet1!R3)"

how do i add in a "Sheet1!R" & rowNumber+1 to the back of the seriescollection.
*assuming the rowNumber is a variable from the last row, in this case an integer of 3

so it will be something like

ActiveChart.SeriesCollection(1).Values = ActiveChart.SeriesCollection(1).Values & "Sheet1!R" & rowNumber+1

somehow the i cant edit the variant to remove the ")" at the back to insert my new Variable row and then inserting back the ")"

Hope someone can help me out here =). Thanks
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are you really wanting to plot entire rows, and do you really want to treat a contiguous range as noncontiguous?

It's tricky to get the range used by a Series, as explained here:

http://www.j-walk.com/ss/excel/tips/tip83.htm

Here is an example of how to build a formula string:

Code:
Sub Test()
    Dim LastRow As Long
    Dim First As Boolean
    Dim rowNumber As Long
    Dim strFormula As String
    LastRow = Range("A65536").End(xlUp).Row
    First = True
    For rowNumber = 1 To LastRow
        If First = True Then
            strFormula = "Sheet1!R" & rowNumber
            First = False
        Else
            strFormula = strFormula & "," & "Sheet1!R" & rowNumber
        End If
    Next rowNumber
    ActiveChart.SeriesCollection(1).Values = "=(" & strFormula & ")"
End Sub
 

specs

New Member
Joined
Oct 26, 2005
Messages
27
Thanks Andrew for your reply. Very informative.
I will have to take some time checking it out. But should get my task going.
And yea.. my trend chart is plotting entire rows..starting from last year..the days can easily fills up the whole row. Im not sure what u meant by my range not being contiguous?
 

Forum statistics

Threads
1,078,094
Messages
5,338,193
Members
399,212
Latest member
Braincraft

Some videos you may like

This Week's Hot Topics

Top