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)?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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

ADVERTISEMENT

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,938
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top