Trying to define a range for a graph dynamically within a macro
 

Hello all,

I've been trying to define a range programmatically so that I can set up a
graph based on the range. The number of rows in the range will vary
depending on when the macro is run. I haven't found a way to define the
range within the macro programming language. There doesn't seem to be a
property on the range spec. Any ideas of how to do this.

Alternatively, I've attempted to use the offset values to determine the
range to be included in the graph but haven't gotten this to work.

Anyone have any ideas about how to do either of these things (or another
approach) so I can have a dynamically set range area for a graph.

Thanks,

JB

Trying to define a range for a graph dynamically within a macro
 

Hi
I'll assume you have one chart embedded in sheet1 - this is
ChartObjects(1). I'll also assume you are also only plotting one
series of data on the chart - this is SeriesCollection(1).
Create a named range called myRange in your workbook like this

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$1),
2)

This is two columns of data (2 at the end), with the top left cell in
A2 (I'll assume you have headings in A1 and B1). The

COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$1)

bit counts the number of data points in the A column minus the heading
row. This requires there be NO GAPS in your column A data. To create
this name do INSERT, NAME, DEFINE in Excel, give it the name myRange
and put the formula in the refers to box.
Now create your chart in the normal way, selecting your data in
columns A and B.
When you add new data to columns A and B, run this macro and your
graph will update. You can also call this macro form Sheet1's
Worksheet_Change event macro so that it is called automatically.

Sub Tester()
Dim Cht As Chart
Set Cht = Worksheets("Sheet1").ChartObjects(1).Chart
With Cht
..SeriesCollection(1).Delete
..SetSourceData Source:=Sheets("Sheet1").Range("myRange")
End With
DoEvents
End Sub

regards
Paul

On Mar 20, 6:12 am, jb wrote:
Hello all,

I've been trying to define a range programmatically so that I can set up a
graph based on the range. The number of rows in the range will vary
depending on when the macro is run. I haven't found a way to define the
range within the macro programming language. There doesn't seem to be a
property on the range spec. Any ideas of how to do this.

Alternatively, I've attempted to use the offset values to determine the
range to be included in the graph but haven't gotten this to work.

Anyone have any ideas about how to do either of these things (or another
approach) so I can have a dynamically set range area for a graph.

Thanks,

JB

Trying to define a range for a graph dynamically within a macr
 

Hi Paul,

For the most part, and with some modifications, I got this to work on
creation of the graph. However, there are a couple of problems. For one
thing, my graph is based on dynamic data calculated within a macro. However,
once I enter the offset for the range definition, the graph takes it as an
initial set of data but it doesn't change it.

I could conceivably just recalculate the range but There's another problem.
The exception is in the graph itself. I'm using a Line - Column on 2 Axis
graph. My Leftmost column is Year data, and they're to be used only for
labeling the graph. I include it when setting up the graph, then I go in
under Source Data, Remove the Year series, and enter a second range under
Category (X) axis Labels. I set up a second range using the method you'd
suggested. However, it will not recognize the name in this databox.

I'm wondering if its possible to use the Macro to directly set this label.
When I try to do this with a macro.

I recorded a macro and got:
ActiveChart.SeriesCollection(1).XValues = "=GRAPHDATA!R3C1:R64C1"

I've tried making this dynamic by replacing it with:
ActiveChart.SeriesCollection(1).XValues = Range(Cells(2, 1), Cells(r, 5))
but it will not accept this.

The easiest way I think of solving this is if I knew how to concatenate
calculated cell numbers into whatever methods I'm using, such as above. I
don't know how to do that, or if its possible in this language, as it is in
others. Do you know how to do that?

Is there some way to use calculated values from a macro to define the range
in the above call.

" wrote:

Hi
I'll assume you have one chart embedded in sheet1 - this is
ChartObjects(1). I'll also assume you are also only plotting one
series of data on the chart - this is SeriesCollection(1).
Create a named range called myRange in your workbook like this

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$1),
2)

This is two columns of data (2 at the end), with the top left cell in
A2 (I'll assume you have headings in A1 and B1). The

COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$1)

bit counts the number of data points in the A column minus the heading
row. This requires there be NO GAPS in your column A data. To create
this name do INSERT, NAME, DEFINE in Excel, give it the name myRange
and put the formula in the refers to box.
Now create your chart in the normal way, selecting your data in
columns A and B.
When you add new data to columns A and B, run this macro and your
graph will update. You can also call this macro form Sheet1's
Worksheet_Change event macro so that it is called automatically.

Sub Tester()
Dim Cht As Chart
Set Cht = Worksheets("Sheet1").ChartObjects(1).Chart
With Cht
..SeriesCollection(1).Delete
..SetSourceData Source:=Sheets("Sheet1").Range("myRange")
End With
DoEvents
End Sub

regards
Paul

On Mar 20, 6:12 am, jb wrote:
Hello all,

I've been trying to define a range programmatically so that I can set up a
graph based on the range. The number of rows in the range will vary
depending on when the macro is run. I haven't found a way to define the
range within the macro programming language. There doesn't seem to be a
property on the range spec. Any ideas of how to do this.

Alternatively, I've attempted to use the offset values to determine the
range to be included in the graph but haven't gotten this to work.

Anyone have any ideas about how to do either of these things (or another
approach) so I can have a dynamically set range area for a graph.

Thanks,

JB

Trying to define a range for a graph dynamically within a macr
 

1. Dynamic ranges used to define the entire data range of a chart are
converted to their cell addresses. They only remain dynamic if used to
define individual series X and Y value ranges.

2. You may not have qualified your range properly:

With Worksheets("GRAPHDATA")
ActiveChart.SeriesCollection(1).XValues = .Range(.Cells(2, 1), .Cells(r,
5))
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"jb" wrote in message
...
Hi Paul,

For the most part, and with some modifications, I got this to work on
creation of the graph. However, there are a couple of problems. For one
thing, my graph is based on dynamic data calculated within a macro.
However,
once I enter the offset for the range definition, the graph takes it as an
initial set of data but it doesn't change it.

I could conceivably just recalculate the range but There's another
problem.
The exception is in the graph itself. I'm using a Line - Column on 2 Axis
graph. My Leftmost column is Year data, and they're to be used only for
labeling the graph. I include it when setting up the graph, then I go in
under Source Data, Remove the Year series, and enter a second range under
Category (X) axis Labels. I set up a second range using the method you'd
suggested. However, it will not recognize the name in this databox.

I'm wondering if its possible to use the Macro to directly set this label.
When I try to do this with a macro.

I recorded a macro and got:
ActiveChart.SeriesCollection(1).XValues = "=GRAPHDATA!R3C1:R64C1"

I've tried making this dynamic by replacing it with:
ActiveChart.SeriesCollection(1).XValues = Range(Cells(2, 1), Cells(r,
5))
but it will not accept this.

The easiest way I think of solving this is if I knew how to concatenate
calculated cell numbers into whatever methods I'm using, such as above. I
don't know how to do that, or if its possible in this language, as it is
in
others. Do you know how to do that?

Is there some way to use calculated values from a macro to define the
range
in the above call.

" wrote:

Hi
I'll assume you have one chart embedded in sheet1 - this is
ChartObjects(1). I'll also assume you are also only plotting one
series of data on the chart - this is SeriesCollection(1).
Create a named range called myRange in your workbook like this

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$1),
2)

This is two columns of data (2 at the end), with the top left cell in
A2 (I'll assume you have headings in A1 and B1). The

COUNTA(Sheet1!$A:$A)-COUNTA(Sheet1!$A$1:$A$1)

bit counts the number of data points in the A column minus the heading
row. This requires there be NO GAPS in your column A data. To create
this name do INSERT, NAME, DEFINE in Excel, give it the name myRange
and put the formula in the refers to box.
Now create your chart in the normal way, selecting your data in
columns A and B.
When you add new data to columns A and B, run this macro and your
graph will update. You can also call this macro form Sheet1's
Worksheet_Change event macro so that it is called automatically.

Sub Tester()
Dim Cht As Chart
Set Cht = Worksheets("Sheet1").ChartObjects(1).Chart
With Cht
..SeriesCollection(1).Delete
..SetSourceData Source:=Sheets("Sheet1").Range("myRange")
End With
DoEvents
End Sub

regards
Paul

On Mar 20, 6:12 am, jb wrote:
Hello all,

I've been trying to define a range programmatically so that I can set
up a
graph based on the range. The number of rows in the range will vary
depending on when the macro is run. I haven't found a way to define
the
range within the macro programming language. There doesn't seem to be
a
property on the range spec. Any ideas of how to do this.

Alternatively, I've attempted to use the offset values to determine the
range to be included in the graph but haven't gotten this to work.

Anyone have any ideas about how to do either of these things (or
another
approach) so I can have a dynamically set range area for a graph.

Thanks,

JB