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 Whe=xlLocationAsObject, _
Name:=ws.Name
ActiveChart.HasLegend = False
End Sub

Any guidance would be greatly appreciated

Creating a Macro to Chart two columns
 

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 Whe=xlLocationAsObject, _
Name:=ws.Name
ActiveChart.HasLegend = False
End Sub

Any guidance would be greatly appreciated

Creating a Macro to Chart two columns
 

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 Whe=xlLocationAsObject, _
Name:=ws.Name
ActiveChart.HasLegend = False
End Sub

Any guidance would be greatly appreciated

Creating a Macro to Chart two columns
 

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 Whe=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 Whe=xlLocationAsObject, _
Name:=ws.Name
ActiveChart.HasLegend = False
End Sub

Any guidance would be greatly appreciated