Setting chart data range automatically

[LoucaGreen]

Hi from Loucas,

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

 

[bobf]

I think you will have to define 2 dynamic ranges, using
offset. This is too complicated to describe here, but if
you e-mail me on (e-mail address removed) I will send you
a spreadsheet that does this without using any vba code.

Bobf

 

[Jon Peltier]

Loucas -

I found a little syntax problem:

ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns

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

 

[LoucaGreen]

Hi,

Thanks a million. It works perfectly.

Do you know of a good reference for Excel VBA, where I can fin
solutions to such problems on my own (i.e. a book or an onlin
reference)?

Thanks again

LoucaGree

 

[Jon Peltier]

The archives of these groups are found in Google:

http://www.google.com/advanced_group_search.

I find other sources using Google as well. There are a few books on
Excel VBA, and Debra Dalgleish has posted a list:

http://www.contextures.com/xlbooks.html

I have Walkenbach's Power Programming; Green, Bullen, Bovey, and
Rosenberg's Programmer's Reference; and Getz and Gilbert's Developer's
Handbook. All are in her list, and I refer to them all.

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