Data series

I am writing a macro that changes the series of charts. I have had big
trouble in getting this to work, forcing me to manually delete and then
repaste series on a daily basis to get it to work. Does anyone have any tips
regarding what to do to get the series to change I would be very happy. I
have some questions in this field:
1) My code now simply sets new values like this:

ActiveChart.SeriesCollection(priceArray(k)).Values =
Sheets(priceArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)

Is this recommended or is there ant other way you should do it e.g.
SetSourceData or something?

2) I have tried using substitute/replace? Is that better? Basically if
anyone has any tips in this area I would be very grateful! Thank you all very
much!

Depends on how your data looks. If the data stays relatively the same, ie
the stuff you want to graph will always be in column 1, then you can define a
dynamic range for the column. Here's an example that I use,

ActiveWorkbook.Names.Add Name:="Xvalues1", RefersToR1C1:="=OFFSET('graph
data'!R6C1,0,0,COUNTA('graph data'!R1C1:R65536C1),1)"

This, when run, will continually update and redefine the range for you
(hence the counta section). So if your data is staying in the same column,
use this and just run a macro to refresh the dynamic range. Then just set
the series data equal to your named range (Xvalues1 in this case). You can
also check and make sure the dynamic range is what you want by clicking
control-F3

cheers,
Scott

"Arne Hegefors" wrote:

> I am writing a macro that changes the series of charts. I have had big
> trouble in getting this to work, forcing me to manually delete and then
> repaste series on a daily basis to get it to work. Does anyone have any tips
> regarding what to do to get the series to change I would be very happy. I
> have some questions in this field:
> 1) My code now simply sets new values like this:
>
> ActiveChart.SeriesCollection(priceArray(k)).Values =
> Sheets(priceArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
> rng(k).Offset(j, 0).Address)
>
> Is this recommended or is there ant other way you should do it e.g.
> SetSourceData or something?
>
> 2) I have tried using substitute/replace? Is that better? Basically if
> anyone has any tips in this area I would be very grateful! Thank you all very
> much!
>