Creating a Macro to Chart two columns

I 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, BUT, the trick is that
sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
etc etc etc.

I found this to start with, but need a little help getting this tweaked for
my needs.

Sub Macro1()
'
Dim rng As Range
Dim ws As Worksheet
'
Set rng = ActiveCell.Range("A1:O1")
Set ws = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData _
Source:=rng, PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=ws.Name
ActiveChart.HasLegend = False
End Sub

Any guidance would be greatly appreciated

Hi,
It is difficult to tell what you are trying to chart here. It looks like the
data is in columns A to D, but the macro looks like you are charting A1:O1.
What is the basis of the budget, is this is monthly, yearly or a per "Amt
Spent" budget?

Thanks,

"Diane" wrote:

> I 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, BUT, the trick is that
> sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
> etc etc etc.
>
> I found this to start with, but need a little help getting this tweaked for
> my needs.
>
> Sub Macro1()
> '
> Dim rng As Range
> Dim ws As Worksheet
> '
> Set rng = ActiveCell.Range("A1:O1")
> Set ws = ActiveSheet
> Charts.Add
> ActiveChart.ChartType = xlLine
> ActiveChart.SetSourceData _
> Source:=rng, PlotBy:=xlRows
> ActiveChart.Location Where:=xlLocationAsObject, _
> Name:=ws.Name
> ActiveChart.HasLegend = False
> End Sub
>
> Any guidance would be greatly appreciated

David,

The budget is per Project, so it is set up once & doesn't change. Basically
by charting the cummulative amount vs. the budget (which is a flat line), it
is a visual of how close the client is getting to the budgeted amount of the
project (or if he goes over). So Series 1 is the cumm amount, series 2 is
the budgeted (flat line) amount & it is plotted by date.
"David" wrote:

> Hi,
> It is difficult to tell what you are trying to chart here. It looks like the
> data is in columns A to D, but the macro looks like you are charting A1:O1.
> What is the basis of the budget, is this is monthly, yearly or a per "Amt
> Spent" budget?
>
> Thanks,
>
> "Diane" wrote:
>
> > I 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, BUT, the trick is that
> > sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
> > etc etc etc.
> >
> > I found this to start with, but need a little help getting this tweaked for
> > my needs.
> >
> > Sub Macro1()
> > '
> > Dim rng As Range
> > Dim ws As Worksheet
> > '
> > Set rng = ActiveCell.Range("A1:O1")
> > Set ws = ActiveSheet
> > Charts.Add
> > ActiveChart.ChartType = xlLine
> > ActiveChart.SetSourceData _
> > Source:=rng, PlotBy:=xlRows
> > ActiveChart.Location Where:=xlLocationAsObject, _
> > Name:=ws.Name
> > ActiveChart.HasLegend = False
> > End Sub
> >
> > Any guidance would be greatly appreciated

Hi,
Hope this will work well enough for you.
Sub Macro3()
Range("D1").Select
ThisSheet = ActiveSheet.Name
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
Range("A1:A" & (LastRow) & ",C1:D" & (LastRow)).Select
Range("C1").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(ThisSheet).Range("A1:A" &
(LastRow) & ",C1:D" & (LastRow)), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:=(ThisSheet)
End Sub

Thanks,

"Diane" wrote:

> David,
>
> The budget is per Project, so it is set up once & doesn't change. Basically
> by charting the cummulative amount vs. the budget (which is a flat line), it
> is a visual of how close the client is getting to the budgeted amount of the
> project (or if he goes over). So Series 1 is the cumm amount, series 2 is
> the budgeted (flat line) amount & it is plotted by date.
> "David" wrote:
>
> > Hi,
> > It is difficult to tell what you are trying to chart here. It looks like the
> > data is in columns A to D, but the macro looks like you are charting A1:O1.
> > What is the basis of the budget, is this is monthly, yearly or a per "Amt
> > Spent" budget?
> >
> > Thanks,
> >
> > "Diane" wrote:
> >
> > > I 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, BUT, the trick is that
> > > sometimes there are 5 rows, sometimes there are 16, sometimes there are 20
> > > etc etc etc.
> > >
> > > I found this to start with, but need a little help getting this tweaked for
> > > my needs.
> > >
> > > Sub Macro1()
> > > '
> > > Dim rng As Range
> > > Dim ws As Worksheet
> > > '
> > > Set rng = ActiveCell.Range("A1:O1")
> > > Set ws = ActiveSheet
> > > Charts.Add
> > > ActiveChart.ChartType = xlLine
> > > ActiveChart.SetSourceData _
> > > Source:=rng, PlotBy:=xlRows
> > > ActiveChart.Location Where:=xlLocationAsObject, _
> > > Name:=ws.Name
> > > ActiveChart.HasLegend = False
> > > End Sub
> > >
> > > Any guidance would be greatly appreciated