Automation of Monthly Chart worngly populated if data less than 4 Months (Jan-Mar)

Hi,

I am writing a macro to create Line Makers chart. However, if the monthly data less than 4 months. e.g. Jan, Jan-Feb, Jan-Mar

The chart SERIES will be wrongly populated to horizontal data rather than Vertical data (Accurate) .


For details of code, please refer to attachment. For Jan-April & onwards, it working fine.

Please Login or Register  to view this content.

the problem is here:

Please Login or Register  to view this content.

You just rely on Excel to setup series. And it works well for you if there is more rows than columns of data. Unfortunately sometimes there is more columns than rows in data range

So instead of using whole data source, add series one by one.

No, no, no, as I said - no SetSourceData.
Try:

Please Login or Register  to view this content.

Note, that I left only rng_1 as 2 columns AB (label and value - wanted to avoid setting SeriesCollection(1).XValues = "=Chart!$A$2:$A$lastrow" ) - all other ranges are 1 column only (resp. C and D)
and in main part we add not a whole datasorce, but series by series:

Please Login or Register  to view this content.

would be nice to have all variables declared, so add above declarations of 2 extra range type variables used

Please Login or Register  to view this content.

Shall work flawlessly now.

Hi Kaper,

Thanks a lot. It working fine, but a small problem when only January data available, X-Axis only display "1" rather than "Jan" for Jan-Feb & onward, it working fine


One more problem Using add series collection, is the 2nd chart populated wrongly. e.g if I create 3 chart in the same worksheets, the 1st and 3rd chart working fine but the 2nd chart populated wrongly.