CODEWITHSUNDEEP
vbaexcel
Pierre44
Pierre44

Reputation: 1741

Changing the Y-Values in Chart series Data

I would like to add one more column to the data of my chart, just like in the question:
Move Chart series data via VBA
However, his code is changing the Y value of the chart and I can't find how to change the X-value

Dim ss As Series
Dim strs() As String
ActiveSheet.ChartObjects("Chart 3").Activate
Set ss = ActiveChart.SeriesCollection(2)
strs = Split(ss.Formula, ",")
Dim rg As Range
Set rg = Range(strs(1))
Set rg = rg.Resize(, rg.Columns.Count + 1)


ActiveChart.SeriesCollection(2).Values = rg

The code I use now is changing the range of the Y axe, giving it the range of the X axe (putting it to the exact size of the Y range +1).
EDIT QUESTION: How can I change the value of the X axe? How can I change the value of the Y axe? Thanks for any help

Upvotes: 0

Views: 1452

Answers (3)

Pierre44
Pierre44

Reputation: 1741

Thanks to Jon Peltier's answer, I developed the following answer: 

    Set ss = ActiveChart.SeriesCollection(2)
    strs = Split(ss.Formula, ",")
    Dim rg As Range
    Set rg = Range(strs(1))
    Set rg = rg.Resize(, rg.Columns.Count + 1)
    Dim strx() As String
    strx = Split(strs(1), "!")

    ActiveChart.SeriesCollection(2).Formula = strs(0) & "," & strx(0) & "!" & rg.Address & "," & strs(2) & "," & strs(3)

It seemed like the only way to change the X values

Upvotes: 0

TomJohn
TomJohn

Reputation: 747

To change X-axis try as follows (it will move x-values to one column to the right):

Sub test()

Dim ss As Series
Dim strs() As String
ActiveSheet.ChartObjects("Chart 3").Activate
Set ss = ActiveChart.SeriesCollection(2)
strs = Split(ss.Formula, ",")
Dim rg As Range
Set rg = Range(strs(1))   ' X-values
Set rg = rg.Offset(0, 1)  ' moving one column to the right
rg.Select                 ' Selecting range for testing purposes

ActiveChart.SeriesCollection(2).XValues = rg
'
End Sub

Hope that helps.

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6073

The series formula looks like this:

=SERIES(Summary!$B$1,Summary!$A$2:$A$4,Summary!$B$2:$B$4,1)

so the elements of your array are:

strs(0): =SERIES(Summary!$B$1 is mostly the series name,
strs(1): Summary!$A$2:$A$4 are the X values,
strs(2): Summary!$B$2:$B$4 are the Y values,
strs(3): 1) is the plot order and the closing paren.

define your rangelike this:

Set rg = Range(strs(2))

and not strs(1), and you'll modify the Y values.

Upvotes: 3

Related Questions