Automatically Resizing a Graph

A Hlookup from another sheet provides the data, and displays it on a graph.
The dates used for the Hlookup are provided by a user, these dates can be
anywhere from 1 week to 6 months apart.
The Graph as it is mostly blank at the moment when set to 2 weeks as it
shows the other 5 and a half months as blank. I thought a Macro might sort
the problem for me, so on the Options tab there is a button that runs the
macro, I had hoped that I could use my limited (very limited!) knowledge of
VBA to change a recorded macro.
To that end, on the Data sheet in cell B99 is a =concatenate that currently
reads =B101:K101 the K part dependant on how much data is present.
I recorded a macro where I changed the graphs "Category (X) axis labels:" to
=B101:K101 when I opened the macro it looked alot like
Sub Resize_Chart()
ActiveSheet.ChartObjects("Chart 28").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(2).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(3).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(4).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(5).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(6).XValues = "=Data!R101C2:R101C11"
ActiveChart.SeriesCollection(7).XValues = "=Data!R101C2:R101C11"
End Sub

And I have no idea how to change this to use the range in Cell B99 on the
Data sheet... any idea's?

Nevermind, once I figured out what the whole R101C2:R101C11 ment I changed
the concatenate to be similar and a inset worked :p

"PaulW" wrote:

> A Hlookup from another sheet provides the data, and displays it on a graph.
> The dates used for the Hlookup are provided by a user, these dates can be
> anywhere from 1 week to 6 months apart.
> The Graph as it is mostly blank at the moment when set to 2 weeks as it
> shows the other 5 and a half months as blank. I thought a Macro might sort
> the problem for me, so on the Options tab there is a button that runs the
> macro, I had hoped that I could use my limited (very limited!) knowledge of
> VBA to change a recorded macro.
> To that end, on the Data sheet in cell B99 is a =concatenate that currently
> reads =B101:K101 the K part dependant on how much data is present.
> I recorded a macro where I changed the graphs "Category (X) axis labels:" to
> =B101:K101 when I opened the macro it looked alot like
> Sub Resize_Chart()
> ActiveSheet.ChartObjects("Chart 28").Activate
> ActiveChart.PlotArea.Select
> ActiveChart.SeriesCollection(1).XValues = "=Data!R101C2:R101C11"
> ActiveChart.SeriesCollection(2).XValues = "=Data!R101C2:R101C11"
> ActiveChart.SeriesCollection(3).XValues = "=Data!R101C2:R101C11"
> ActiveChart.SeriesCollection(4).XValues = "=Data!R101C2:R101C11"
> ActiveChart.SeriesCollection(5).XValues = "=Data!R101C2:R101C11"
> ActiveChart.SeriesCollection(6).XValues = "=Data!R101C2:R101C11"
> ActiveChart.SeriesCollection(7).XValues = "=Data!R101C2:R101C11"
> End Sub
>
> And I have no idea how to change this to use the range in Cell B99 on the
> Data sheet... any idea's?