Altering the range that is plotted by a chart via VBA

[Guest]

Good morning all.

I haven't really done a huge amount on charting, so I'm sure you'll think
that these questions are a bit simplistic, but here goes!

I plot a chart from a worksheet that contains three columns of data - the X
axis contains week numbers, and is in B29:B54. Actual hours are entered in
F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the
form: =SUM($F$29:F29)

So, each week, in order to plot another week's data, I have to copy this
cumulative formula down by one cell, which, considering I have a lot of these
reports, is a bit time consuming.

If I replace my cumulative formula with something that displays a zero or a
label if there is no corresponding weekly actual, the cumulative chart line
plummets down to zero, which makes the charts look unprofessional. If I
replace it with something that picks up the previous week's cumulative value
if there is no corresponding weekly actual, the cumulative chart line goes
across at a value equal to the latest cumulative, which also doesn't look too
good.

So, the question is, how, in this week for example, can I have my chart area
as b29:B54 (x-axis);f29:f54 (actuals); g29:f40 (cumulatives), but next week
extend
my cumulative plot to g29:f41 (i.e. extend it down by one week)?

Eventiually, I'd like to etend this idea to plotting a rolling 12 week
chart, so to start, it would plot b29:B38 (x-axis);f29:f38 (actuals); g29:f38
(cumulatives) then the next week, b30:B39 (x-axis);f30:f39 (actuals); g30:f39
(cumulatives) and so on, but one thing at a time.

If anyone has any good pointers as to how I might achieve some or all of the
above, I'd be extremely grateful.

Regards & thanks in advance.

Pete

 

[Tushar Mehta]

Adapt the various ideas at
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[tubbinator]

Got a reply off of the Excel-L mailing list. Had to move th
".ChartType = xlLine" line up higher. Moved it to right after char
creation. I could have sworn I'd gotten errors with that line u
higher before. Oh well.

Tubb

 

[Jon Peltier]

You replied to the wrong thread.

Sometimes putting .ChartType up too high will cause an error. If you specify a
bubble of stock chart type before specifying enough data to populate that chart
type, it will crash.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Thanks to all who replied.
I eventually achieved what I wanted by plotting the chart against named
ranges, and using VBA to redefine the ranges to which those named applied.
The charts automtically updated based on the redefined ranges.
I shouldn't have been so lazy!

Regards & have a good weekend.

Pete