vba chart problem.

[Lochnagar]

hi

I have a vba question/problem regarding inputing the xvalues and yvalues for a data series into a chart using vba.

I used the macro recorder and manually inputed into the 'Source Data' window/menu of the chart the x and y values for the data series, which generated the following...

ActiveChart.SeriesCollection(2).XValues = "='Recycle valves'!R5C3:R32003C3"
ActiveChart.SeriesCollection(2).Values = "='Recycle valves'!R5C10:R32003C10"

As I have a lot of data series that I need to input into the chart I was hoping that I could use the basic outline of the code above in a For Next loop see below...

for i = 1 to 5
ActiveChart.SeriesCollection(i).XValues = "='Recycle valves'!R5Ci:R32003C3"
ActiveChart.SeriesCollection(i).Values = "='Recycle valves'!R5Ci:R32003C10"
Next

...such that for each new series that is added to the chart the column that is referenced in the worksheet is offset for each data series e.g. if i = 1 that I'd get R5C1:R320... or if i = 4 I'd get R5C4:R320... etc.

I have tried the above, but to no avail and I was hoping that someone might be able to point me in the right direction in terms of achieving the above...assuming it is possible.

Any suggestions/advice etc will be greatly appreciated.

Thanks,

Lochnagar

 

[Andrew Poulsom]

Try:

For i = 1 to 5
    ActiveChart.SeriesCollection(i).XValues = "='Recycle valves'!R5C" & i & ":R32003C3"
    ActiveChart.SeriesCollection(i).Values = "='Recycle valves'!R5C" & i & ":R32003C10"
Next

 

[Lochnagar]

Brilliant! Just what I was after. Thanks Andrew. Really appreciate your help.

Regards,

Lochnagar