X values on a chart

[Bobby]

Could someone give my a hand on this:
I am trying to set a range on a graph but the result does not reflect
the range values.
Here is the code:
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection.NewSeries
.Values = Sheets("Planif-plan").Range("H2:H79")
End With

I would like to see:
22 23 24 25 26 27 up to 52 and 1 2 3 4 up to 47
This represent 78 weeks of planning

What I get is:
1 to 78
Can this be fix?
Thank you!

 

[Peter T]

Put your category values in (say) G2:H79, eg

wks = 78
wkStart = 22
For i = wkStart To wkStart + wks - 1
Range("G" & 2 + n) = (i - 1) Mod 52 + 1
n = n + 1
Next

and add to the first series in the chart
..XValues = Sheets("Planif-plan").Range("G2:G79")

Regards,
Peter T

 

[OssieMac]

Hi Bobby,

Combining your subject line with the text of your message I am assuming the
it is the X axis values that are returning sequential numbers 1-78. Is this
correct?

If my assumption is correct then you need to tell Excel the range to use for
the X Axis. Unfortunately I can't give you a full answer because I do not
know how to identify the Series Index when a new series is added as per your
code. Perhaps someone else can expand on this answer and tell us both how to
identify the series index.

The code to set the range to use for the X Axis when the series index is
known is:-

ActiveChart.SeriesCollection(1).XValues = "='Planif-plan'!R2C7:R79C7"

I have assumed that the x axis range is in column G or (7th column)

 

[Jon Peltier]

If the numbers are in the order you want, then make a line chart rather than
an XY chart. The line chart treats everything, even numbers, as nonnumeric
labels.

- Jon

 

[Bobby]

If the numbers are in the order you want, then make a line chart rather than
an XY chart. The line chart treats everything, even numbers, as nonnumeric
labels.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.http://PeltierTech.com/WordPress/
_______








- Show quoted text -

Thank's Jon