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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
The XValues property returns a variant array:

Code:
Dim s1 As Variant
s1 = ActiveChart.SeriesCollection(1).XValues
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top