Chart with dynamic x-axis names

All,
This is a repost. I put it in excel.charts earlier and wasn't getting
much of a response. Let's see how it goes here....

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,

See this excelent articlefrom J. Peltier

http://pubs.logicalexpressions.com/P...cle.asp?ID=246

rg
Mika

article Thank you, but I am using VB to code this. I read that previously,
but I can't just enter formulas in the spreadsheet. A macro will be used,
and everytime it is run it will wipe the page. Of course, I may just be
missing the big picture here...

"Mika" wrote:

> Hi,
>
> See this excelent articlefrom J. Peltier
>
> http://pubs.logicalexpressions.com/P...cle.asp?ID=246
>
> rg
> Mika
>
>

Thank you, but I am using VB to code this. I read that article previously,
but I can't just enter formulas in the spreadsheet. A macro will be used,
and everytime it is run it will wipe the page. Of course, I may just be
missing the big picture here...

"Mika" wrote:

> Hi,
>
> See this excelent articlefrom J. Peltier
>
> http://pubs.logicalexpressions.com/P...cle.asp?ID=246
>
> rg
> Mika
>
>

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,
> This is a repost. I put it in excel.charts earlier and wasn't getting
> much of a response. Let's see how it goes here....
>
> 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

http://support.microsoft.com/default...40&Product=xlw
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default...80&Product=xlw
XL2000: How to Use Visual Basic to Create a Dynamic Chart

http://support.microsoft.com/default...55&Product=xlw
Using Visual Basic to Create a Chart Using a Dynamic Range


--
Regards,
Tom Ogilvy

"Jon" <[email protected]> wrote in message
news:[email protected]...
> Thank you, but I am using VB to code this. I read that article
previously,
> but I can't just enter formulas in the spreadsheet. A macro will be used,
> and everytime it is run it will wipe the page. Of course, I may just be
> missing the big picture here...
>
> "Mika" wrote:
>
> > Hi,
> >
> > See this excelent articlefrom J. Peltier
> >
> > http://pubs.logicalexpressions.com/P...cle.asp?ID=246
> >
> > rg
> > Mika
> >
> >