Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Automatically changing the range in a graph | Charts and Charting in Excel | |||
Hyperlinkage of one graph with another graph or Drill down graph | Charts and Charting in Excel | |||
Graph colors automatically change | Charts and Charting in Excel | |||
How do I automatically update a graph after adding a data point | Charts and Charting in Excel |