software

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 quot;Category (X) axis labels:quot; to
=B101:K101 when I opened the macro it looked alot like
Sub Resize_Chart()
ActiveSheet.ChartObjects(quot;Chart 28quot;).Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = quot;=Data!R101C2:R101C11quot;
ActiveChart.SeriesCollection(2).XValues = quot;=Data!R101C2:R101C11quot;
ActiveChart.SeriesCollection(3).XValues = quot;=Data!R101C2:R101C11quot;
ActiveChart.SeriesCollection(4).XValues = quot;=Data!R101C2:R101C11quot;
ActiveChart.SeriesCollection(5).XValues = quot;=Data!R101C2:R101C11quot;
ActiveChart.SeriesCollection(6).XValues = quot;=Data!R101C2:R101C11quot;
ActiveChart.SeriesCollection(7).XValues = quot;=Data!R101C2:R101C11quot;
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

quot;PaulWquot; wrote:

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