Repetitive charting on same graph

[hegdep]

This is what I am trying to do, tracing one single graph by picking out data from a repeated interval. I've attached an example file with the chart I want to generate.

The logic is quite clean cut. But, I am not really sure how to translate it to VBA syntax.
Basically, on the Active sheet, I want to perform the following operation.

I have a repeating template of values.

Floating cell= C4
Floating range= FloatingCell to FloatingCell.offset(20,1)
Chart the same with Q2 on x axis, Q 3 on Y axis.
series name= D1

Floating cell=C4+24
Again, the corresponding range and chart it out on the same initial graph.
Series name=D1 offset by 24 on the column.

The charting should end when the floating cell basically find a value when it keeps going down. I have only 5 data sets in the example file here. But, the code is to enable me to deal with 500-600 such data sets.

To add to the complication, I am developing a macros which will dynamically move the sheet down. i.e. when a new data set is taken, the latest one gets pasted on top and all the other values move down. I want the graphing to be able to accommodate and trace the latest values too. I am thinking clearing the graph and rerunning the loop is one way out. But, there might be something more efficient.

[sweep]

Hi,

The following code could be a place to start for you?

Please Login or Register  to view this content.

While it's not necessarily efficient, I would delete the whole chart and start again when new data is added. That can be achieved by adding this line to the start of the code

Please Login or Register  to view this content.

[NickyC]

or a slight variation creating a new chart

Please Login or Register  to view this content.

[hegdep]

@Sweep

This is a very good starting point. It could be bettered if I can loop it to many sheets.
The only downside I see is that this needs a preset chart to work. If the naming of the chart could be set to the sheet name, it would make it a lot easier.
I'll try to work with it.
Thanks!

[hegdep]

or a slight variation creating a new chart

Please Login or Register  to view this content.

This seems to be a more well organized code, logically.
However, when I try to run it, I get an error message saying "object doesn't support this property or method". I am presuming this has got to do with the type of graph selected.
When I remove the AddChart2 and make it plainly AddChart, it traces up a histogram. Is there any reference about the codes for the types of graphs? If yes, it would be helpful to have one.

[hegdep]

Hi,

The following code could be a place to start for you?

Please Login or Register  to view this content.

While it's not necessarily efficient, I would delete the whole chart and start again when new data is added. That can be achieved by adding this line to the start of the code

Please Login or Register  to view this content.

What is the syntax I must use to run this code with ActiveSheet instead of using Sheet 1? Especially to replace "=Sheet1!$D$"
Thanks for your help?

[NickyC]

Hi
your problem with my code could be a version issue. Try this variation:

Please Login or Register  to view this content.

[sweep]

What is the syntax I must use to run this code with ActiveSheet instead of using Sheet 1? Especially to replace "=Sheet1!$D$"
Thanks for your help?

I guess this will do what you need.

Please Login or Register  to view this content.

If you wanted to create a new chart, and name it relative to the sheet name, you'd need somecode like this at the start of the routine...

Please Login or Register  to view this content.

To loop through all worksheets, wrap a for....each....in.....next loop around the code

and refer to the sheet ws in the code

Please Login or Register  to view this content.

Altogether, this might be work for you.

Please Login or Register  to view this content.

[hegdep]

Thanks a ton! I figured it out earlier today, it was a little more complicated than that, because I had to play between sheets. I really appreciate you breaking this down for me.