Setting chart data range automatically
 

Hi,

This is my second posting to the forum. I hope someone out there ca
help me!

I am trying to set the data range for my chart automatically. Som
macros change the data in my worksheet with the data, and as a resul
my chart data range may need to cover more/less rows and/or columns.

So, every time I run the macros for my data worksheet, I need anothe
macro to re-define the data range for my graph.

More info:
1. I have variables that reflect the column and row numbers for m
range that I can use
2. The data range does not consist of consecutive cells (there is a ga
of data columns that should not be in the graph)

I have gone far enough but at the end, something deters my vba cod
from using my range, even though the range is being recognised a
such.

With my code below, I define two separate ranges, and then a third on
that consists of the union of the two. Excel recognises that rang
because it selects it, but does not manage to use it for the graph.

My code:

Sub ChangeChartRange1()

Dim r1 As Range, r2 As Range, ChartRange As Range

Worksheets("ChartData").Activate

Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2))
Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11))
Set ChartRange = Union(r1, r2)

ChartRange.Select

Sheets("Chart").Select
ActiveChart.SetSourceDat
Source:=Sheets("ChartData").Range(ChartRange), PlotBy:=xlColumns

End Sub

When running this code I get:
"Run-time error '1004':
Application-defined or object-defined error"


The following actually works:

Sub ChangeChartRange2()

Dim StringRange As String

StringRange = "B15:B1626,D15:K1626"

Sheets("Chart").Select
ActiveChart.SetSourceDat
Source:=Sheets("ChartData").Range(StringRange), PlotBy:=xlColumns

End Sub

The above works, but is not good enough for me because it requires tha
the data range is a predefined string incorporated in the code, whil
in my case I want it to be dynamically assigned based on some variable
obtained from other code.

The fact that the second code works means that the only problem ther
is with the first version of code is using the range for the graph.

I have tried to be as descriptive as possible.

Does anyone know a way around this?

Many thanks

LoucaGree

--
Message posted from http://www.ExcelForum.com

Setting chart data range automatically
 

Sub ChangeChartRange1()

Dim r1 As Range, r2 As Range, ChartRange As Range

Worksheets("ChartData").Activate

Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2))
Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11))
Set ChartRange = Union(r1, r2)

ChartRange.Select

Sheets("Chart").Select
ActiveChart.SetSourceData
Source:=Sheets("ChartData").Range(ChartRange.Addre ss), PlotBy:=xlColumns

End Sub

======= or =========

Sub ChangeChartRange1()

Dim r1 As Range, r2 As Range, ChartRange As Range

Worksheets("ChartData").Activate

Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2))
Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11))
Set ChartRange = Union(r1, r2)

ChartRange.Select

Sheets("Chart").Select
ActiveChart.SetSourceData
Source:=ChartRange, PlotBy:=xlColumns

End Sub

--
Regards,
Tom Ogilvy


"LoucaGreen " wrote in message
...
Hi,

This is my second posting to the forum. I hope someone out there can
help me!

I am trying to set the data range for my chart automatically. Some
macros change the data in my worksheet with the data, and as a result
my chart data range may need to cover more/less rows and/or columns.

So, every time I run the macros for my data worksheet, I need another
macro to re-define the data range for my graph.

More info:
1. I have variables that reflect the column and row numbers for my
range that I can use
2. The data range does not consist of consecutive cells (there is a gap
of data columns that should not be in the graph)

I have gone far enough but at the end, something deters my vba code
from using my range, even though the range is being recognised as
such.

With my code below, I define two separate ranges, and then a third one
that consists of the union of the two. Excel recognises that range
because it selects it, but does not manage to use it for the graph.

My code:

Sub ChangeChartRange1()

Dim r1 As Range, r2 As Range, ChartRange As Range

Worksheets("ChartData").Activate

Set r1 = Worksheets("ChartData").Range(Cells(15, 2), Cells(1626, 2))
Set r2 = Worksheets("ChartData").Range(Cells(15, 4), Cells(1626, 11))
Set ChartRange = Union(r1, r2)

ChartRange.Select

Sheets("Chart").Select
ActiveChart.SetSourceData
Source:=Sheets("ChartData").Range(ChartRange), PlotBy:=xlColumns

End Sub

When running this code I get:
"Run-time error '1004':
Application-defined or object-defined error"


The following actually works:

Sub ChangeChartRange2()

Dim StringRange As String

StringRange = "B15:B1626,D15:K1626"

Sheets("Chart").Select
ActiveChart.SetSourceData
Source:=Sheets("ChartData").Range(StringRange), PlotBy:=xlColumns

End Sub

The above works, but is not good enough for me because it requires that
the data range is a predefined string incorporated in the code, while
in my case I want it to be dynamically assigned based on some variables
obtained from other code.

The fact that the second code works means that the only problem there
is with the first version of code is using the range for the graph.

I have tried to be as descriptive as possible.

Does anyone know a way around this?

Many thanks

LoucaGreen


---
Message posted from http://www.ExcelForum.com/

Setting chart data range automatically
 

Hi,

Thanks for your reply.

Now I get another error

"Run-time error '9'
Subscript out of range"

Nevermind though. The solution was given to me at the Charting forum.

I had to change th
Source:=Sheets("ChartData").Range(ChartRange.Addre ss)
to Source:=ChartRange.Address.

Thanks

LoucaGree

--
Message posted from http://www.ExcelForum.com

Setting chart data range automatically
 

No, you needed to change

Source:=Sheets("ChartData").Range(ChartRange),

to
Source:=ChartRange.Address


Source:=Sheets("ChartData").Range(ChartRange.Addre ss)

would work as well.


I also gave you that correction. Subscript out of range would indicate that
you don't have the proper name for your sheet.

--
Regards,
Tom Ogilvy



"LoucaGreen " wrote in message
...
Hi,

Thanks for your reply.

Now I get another error

"Run-time error '9'
Subscript out of range"

Nevermind though. The solution was given to me at the Charting forum.

I had to change the
Source:=Sheets("ChartData").Range(ChartRange.Addre ss)
to Source:=ChartRange.Address.

Thanks

LoucaGreen


---
Message posted from http://www.ExcelForum.com/

Setting chart data range automatically
 

Tom -

I think you meant

to
Source:=ChartRange

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


Tom Ogilvy wrote:

No, you needed to change

Source:=Sheets("ChartData").Range(ChartRange),

to
Source:=ChartRange.Address


Source:=Sheets("ChartData").Range(ChartRange.Addre ss)

would work as well.


I also gave you that correction. Subscript out of range would indicate that
you don't have the proper name for your sheet.