Charting with dynamic data

All,
I am creating a chart which is based on multiple columns worth of data.
The problem is that the amount of rows changes and I have to be able to
handle that programmaticaly. In other words, if the amount of rows was
constant somewhere in my sub I would have something like:

..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range("A2:A5, F2:H5")

'where the Range A2:A5 contains names that goes to label the X-axis.
'And F2:H5 represents the data for each name in A2:A5. No problem.

However, I need that range to vary. I was hoping something like this would
work:

.Chart.ChartWizard
Source:=Worksheets(xlSheet(I).Name).Range(xlSheet(I).Cells(2,
1):xlSheet(I).Cells(z, 1), xlSheet(I).Cells(2, 6):xlSheet(I).Cells(z, 8))

'where z is an integer which calculated the number of rows needed. Problem.
'Didn't work

As you can see I tried to mimic the form used for static data but failed
miserably. Any help would be greatly appreciated. Clear as mud? Thanks in
advance.


--
J

Hi Jon,

I would try another procedure: Link the chart with a named
range, e.g.

..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range
("arguments")

Then you can name the arguments range, starting at a known
cell e.g. A2 and down all filled rows with:

..Range("A2", "A" & .Range("A2", "A65534").SpecialCells
(xlCellTypeBlanks).Cells(1).Row - 1).Name = "data"

For info: xlCellTypeBlanks gives you the first empty cell,
therefore you need to use .Row - 1


Best

Markus

Markus,
Thanks for the quick response. I see exactly what you are trying, but I
cannot get my code to run with it. Here's the relevant snippet, tell me if
this is what you had in mind.

Set ch = Worksheets("Charts").ChartObjects.Add(Left:=15, Width:=700, _
Top:=(J * (225 + 25) + 50), Height:=225)
With ch
.Chart.SetSourceData Source:=Sheets(xlSheet(I).Name).Range(xlSheet(I). _
Cells(2, 6), xlSheet(I).Cells(z, 8))
.Chart.Range("A2", "A" & .Range("A2", "A65534").SpecialCells _
(xlCellTypeBlanks).Cells(1).Row - 1).Name = "data"
End With

I end up with a run-time error 438 "Object doesn't support this property or
method"

What am I missing here?

"Markus Scheible" wrote:

> Hi Jon,
>
> I would try another procedure: Link the chart with a named
> range, e.g.
>
> ..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range
> ("arguments")
>
> Then you can name the arguments range, starting at a known
> cell e.g. A2 and down all filled rows with:
>
> ..Range("A2", "A" & .Range("A2", "A65534").SpecialCells
> (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data"
>
> For info: xlCellTypeBlanks gives you the first empty cell,
> therefore you need to use .Row - 1
>
>
> Best
>
> Markus
>

Anyone else have thoughts on this?

"Jon" wrote:

> All,
> I am creating a chart which is based on multiple columns worth of data.
> The problem is that the amount of rows changes and I have to be able to
> handle that programmaticaly. In other words, if the amount of rows was
> constant somewhere in my sub I would have something like:
>
> .Chart.SetSourceData Source:=Sheets("NameGoesHere").Range("A2:A5, F2:H5")
>
> 'where the Range A2:A5 contains names that goes to label the X-axis.
> 'And F2:H5 represents the data for each name in A2:A5. No problem.
>
> However, I need that range to vary. I was hoping something like this would
> work:
>
> .Chart.ChartWizard
> Source:=Worksheets(xlSheet(I).Name).Range(xlSheet(I).Cells(2,
> 1):xlSheet(I).Cells(z, 1), xlSheet(I).Cells(2, 6):xlSheet(I).Cells(z, 8))
>
> 'where z is an integer which calculated the number of rows needed. Problem.
> 'Didn't work
>
> As you can see I tried to mimic the form used for static data but failed
> miserably. Any help would be greatly appreciated. Clear as mud? Thanks in
> advance.
>
>
> --
> J

For all who are interested here is a snippet that provides the solution (I
have no hair now...). UNION was the key.

With ch

..Chart.ChartWizard Source:=Union( _
Worksheets(xlSheet(i).Name).Range(xlSheet(i).Cells(2, 1),
xlSheet(i).Cells(z, 1)), _
Worksheets(xlSheet(i).Name).Range(xlSheet(i).Cells(2, 6),
xlSheet(i).Cells(z, 8))), _
Title:=xlSheet(i).Name, _
PlotBy:=xlColumns, _
CategoryLabels:=1
End With

Thanks to all who thought about it.



"Jon" wrote:

> All,
> I am creating a chart which is based on multiple columns worth of data.
> The problem is that the amount of rows changes and I have to be able to
> handle that programmaticaly. In other words, if the amount of rows was
> constant somewhere in my sub I would have something like:
>
> .Chart.SetSourceData Source:=Sheets("NameGoesHere").Range("A2:A5, F2:H5")
>
> 'where the Range A2:A5 contains names that goes to label the X-axis.
> 'And F2:H5 represents the data for each name in A2:A5. No problem.
>
> However, I need that range to vary. I was hoping something like this would
> work:
>
> .Chart.ChartWizard
> Source:=Worksheets(xlSheet(I).Name).Range(xlSheet(I).Cells(2,
> 1):xlSheet(I).Cells(z, 1), xlSheet(I).Cells(2, 6):xlSheet(I).Cells(z, 8))
>
> 'where z is an integer which calculated the number of rows needed. Problem.
> 'Didn't work
>
> As you can see I tried to mimic the form used for static data but failed
> miserably. Any help would be greatly appreciated. Clear as mud? Thanks in
> advance.
>
>
> --
> J

Jon -

You found something that works. This command in your earlier post must
have been causing major problems:

.Chart.Range("A2", "A" & .Range("A2", "A65534").SpecialCells _
(xlCellTypeBlanks).Cells(1).Row - 1).Name = "data"

Since a Chart has no Range method or property, the line can only lead to
run time errors.

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

Jon wrote:

> Markus,
> Thanks for the quick response. I see exactly what you are trying, but I
> cannot get my code to run with it. Here's the relevant snippet, tell me if
> this is what you had in mind.
>
> Set ch = Worksheets("Charts").ChartObjects.Add(Left:=15, Width:=700, _
> Top:=(J * (225 + 25) + 50), Height:=225)
> With ch
> .Chart.SetSourceData Source:=Sheets(xlSheet(I).Name).Range(xlSheet(I). _
> Cells(2, 6), xlSheet(I).Cells(z, 8))
> .Chart.Range("A2", "A" & .Range("A2", "A65534").SpecialCells _
> (xlCellTypeBlanks).Cells(1).Row - 1).Name = "data"
> End With
>
> I end up with a run-time error 438 "Object doesn't support this property or
> method"
>
> What am I missing here?
>
> "Markus Scheible" wrote:
>
>
>>Hi Jon,
>>
>>I would try another procedure: Link the chart with a named
>>range, e.g.
>>
>>..Chart.SetSourceData Source:=Sheets("NameGoesHere").Range
>>("arguments")
>>
>>Then you can name the arguments range, starting at a known
>>cell e.g. A2 and down all filled rows with:
>>
>>..Range("A2", "A" & .Range("A2", "A65534").SpecialCells
>>(xlCellTypeBlanks).Cells(1).Row - 1).Name = "data"
>>
>>For info: xlCellTypeBlanks gives you the first empty cell,
>>therefore you need to use .Row - 1
>>
>>
>>Best
>>
>>Markus
>>