Setting chart data range automatically

[LoucaGreen]

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

 

[Tom Ogilvy]

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.Address), 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

 

[LoucaGreen]

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.Address)
to Source:=ChartRange.Address.

Thanks

LoucaGree

 

[Tom Ogilvy]

No, you needed to change

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

to
Source:=ChartRange.Address


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

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.

 

[Jon Peltier]

Tom -

I think you meant

to
Source:=ChartRange

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