Excel VBA Automatic Graph Range Assignment

Hello,

I am using Microsoft Excel VBA 2007 for annual energy use simulation over a year, on an hour by hour basis.

This generates arrays of 8760 numbers (no. of hours in a 365 day year) which I store in named ranges on various worksheets.

I am interested in writing a sub-procedure that would identify from the 8760 number range where the peak energy consumption is and plot a graph showing the 24 hour range that this peak value falls within.

I can use a worksheet function to quickly locate the element in the array that holds the max value for the whole named range, but how do I then write something that shifts the min/max value of the x-axis on the graph to the day (i.e. 24hour range) which this max-value falls under?

I am sure this must be possible but am struggling with chart objects at the moment so any help would be much appreciated!

Best Regards,

Francis