Changing chart data ranges from VBA code

[Andrew]

Hello,
I have a VBA program which will be generating a lot of data, and I
want to be able to view the data on a chart. The chart only holds
32000 samples, and my program is likely to generate 100,000 samples.
So, I start out with the source data range being fixed at R1C2 to
R32001C2. But when I get to a count of 31000 samples, I want the data
range to shift up by 1000 rows, so that the new data range would be
R1001C2 to R33000C2. And I want this process to repeat for every 1000
samples. Can someone please send me a snippet of code to perform this
task.

Here is my starting code.

Dim datacount as double
If datacount > 31000 And datacount Mod 1000 = 0 Then

With Worksheets("monitor").ChartObjects(1).Chart
.SeriesCollection(1).XValues = "=data!R1C2:R32000C2"
.SeriesCollection(1).Values = "=data!R1C4:R32000C4"
End with
End If


thanks,
Andy

 

[Don Guillett]

for i=1 to 60000 step 1000
do it
next i

 

[Andrew]

for i=1 to 60000 step 1000
do it
next i

I apologize for poorly describing my question. I know how to create
the loop to find the multiples of 1000. What I don't know is how to
enter "=data!R1C2:R32000C2" in the form of a variable.

 

[Jon Peltier]

You can do this just as easily and more automatically using defined names as
your chart data source, rather than VBA. Here is where to read about dynamic
charts:

http://peltiertech.com/Excel/Charts/Dynamics.html

In particular, you need a minor variation on this technique, which plots the
last twelve months from a longer table:

http://peltiertech.com/Excel/Charts/DynamicLast12.html

- Jon