Changing chart data ranges from VBA code
 

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

Changing chart data ranges from VBA code
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andrew" wrote in message
...
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

Changing chart data ranges from VBA code
 

On Dec 11, 11:35 am, "Don Guillett" wrote:
for i=1 to 60000 step 1000
do it
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Andrew" wrote in message

...

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

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.

Changing chart data ranges from VBA code
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andrew" wrote in message
...
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