Automate creation of charts

Hi all,

I have a spreadsheet of car park occupancy for each hour of the day across 5 car parks. The first 24 rows are hourly occupancies for Car Park A, the next for Car Park B, and so on.
I want a chart of hourly occupancy for each of the 5 car parks in the spreadsheet and automate this process.
I know I'd need a for loop which creates the first chart and then adds 24 rows to the chart's data array, but struggling to get my head around this.

The code I have so far is as follows:

Please Login or Register  to view this content.

Any help would be hugely appreciated

Well first thing I noticed is you told the For Loop to count x from 0 to 5, but at the end of the loop, you add 24 to x, meaning it already exceeds 5 and ends the loop.

Thanks, I see what you mean. Should be "For x=1 To 5" and "x=x+1" but within the code, determining the chart series data could use a range of x*24-22 to x*24+1
So for iteration 1 (x=1), 1*24-22 to 1*24+1 (i.e. row 2 to row 25)
and for iteration 2 (x=2), 2*24-22 to 2*24+1 (i.e. row 26 to row 49)

Just need the code that achieves that!

You might need to enable the Microsoft Scripting Runtime library to use the following macro. To do this, in the VBA editor goto Tools > References. Then scroll til you see "Microsoft Scripting Runtime library" and make sure it's checked. If already checked it will be at the top of the list with the other active references.

Please Login or Register  to view this content.

Hope this helps!

Hero, thanks very much

Less complicated than using a dictionary:

Please Login or Register  to view this content.