VBA for plotting charts: Question re-worded

have the following spreadsheet:

A B C D
1 Date Amt Spent Cumm Amt Budget
2 1/10 100 100 1000
3 1/20 50 150 1000
4 1/30 75 225 1000
5 2/10 100 325 1000

Where Budget (Column D) will always be the same value, and Column C is the
cummulative value of B. I am desperately trying to create a macro where it
will line plot column C & D against column A.The trick is that
sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
etc etc etc.

I think I am close as follows:

Sub Macro17()
'
' Macro17 Macro
' Macro recorded 5/2/2005 by
'
Dim NoRows As Integer

'
Charts.Add
ActiveChart.ChartType = xlLine
NoRows = Cells(Rows.Count, "M").End(xlUp).Row
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("O4:O" &
NoRows) PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Budget"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasDataTable = False
End Sub

But the Dim Statement & SetSourseData lines are incorrect. I was just tring
to plot one first - then a second series - any help would be greatly
appreciated.

In article <[email protected]>,
[email protected] says...
{snip}
>
> But the Dim Statement & SetSourseData lines are incorrect. I was just tring
> to plot one first - then a second series - any help would be greatly
> appreciated.
>
How are they wrong? You wrote them! What do you want them to be?

For what it's worth, you may want to look at a non-programmatic
solution:
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> have the following spreadsheet:
>
> A B C D
> 1 Date Amt Spent Cumm Amt Budget
> 2 1/10 100 100 1000
> 3 1/20 50 150 1000
> 4 1/30 75 225 1000
> 5 2/10 100 325 1000
>
> Where Budget (Column D) will always be the same value, and Column C is the
> cummulative value of B. I am desperately trying to create a macro where it
> will line plot column C & D against column A.The trick is that
> sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
> etc etc etc.
>
> I think I am close as follows:
>
> Sub Macro17()
> '
> ' Macro17 Macro
> ' Macro recorded 5/2/2005 by
> '
> Dim NoRows As Integer
>
> '
> Charts.Add
> ActiveChart.ChartType = xlLine
> NoRows = Cells(Rows.Count, "M").End(xlUp).Row
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("O4:O" &
> NoRows) PlotBy _
> :=xlColumns
> ActiveChart.SeriesCollection(1).Name = "=""Budget"""
> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> ActiveChart.HasDataTable = False
> End Sub
>
> But the Dim Statement & SetSourseData lines are incorrect. I was just tring
> to plot one first - then a second series - any help would be greatly
> appreciated.
>

That was the ticket!!!!! Thank you. Better than coding it!

"Tushar Mehta" wrote:

> In article <[email protected]>,
> [email protected] says...
> {snip}
> >
> > But the Dim Statement & SetSourseData lines are incorrect. I was just tring
> > to plot one first - then a second series - any help would be greatly
> > appreciated.
> >
> How are they wrong? You wrote them! What do you want them to be?
>
> For what it's worth, you may want to look at a non-programmatic
> solution:
> Dynamic Charts
> http://www.tushar-mehta.com/excel/ne...rts/index.html
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>,
> [email protected] says...
> > have the following spreadsheet:
> >
> > A B C D
> > 1 Date Amt Spent Cumm Amt Budget
> > 2 1/10 100 100 1000
> > 3 1/20 50 150 1000
> > 4 1/30 75 225 1000
> > 5 2/10 100 325 1000
> >
> > Where Budget (Column D) will always be the same value, and Column C is the
> > cummulative value of B. I am desperately trying to create a macro where it
> > will line plot column C & D against column A.The trick is that
> > sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
> > etc etc etc.
> >
> > I think I am close as follows:
> >
> > Sub Macro17()
> > '
> > ' Macro17 Macro
> > ' Macro recorded 5/2/2005 by
> > '
> > Dim NoRows As Integer
> >
> > '
> > Charts.Add
> > ActiveChart.ChartType = xlLine
> > NoRows = Cells(Rows.Count, "M").End(xlUp).Row
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("O4:O" &
> > NoRows) PlotBy _
> > :=xlColumns
> > ActiveChart.SeriesCollection(1).Name = "=""Budget"""
> > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
> > ActiveChart.HasDataTable = False
> > End Sub
> >
> > But the Dim Statement & SetSourseData lines are incorrect. I was just tring
> > to plot one first - then a second series - any help would be greatly
> > appreciated.
> >
>

You are welcome.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
> That was the ticket!!!!! Thank you. Better than coding it!
>
{snip}