Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Stacked Columns Chart | Charts and Charting in Excel | |||
Macro creating chart gallery giving problem | Charts and Charting in Excel | |||
Skip some columns when creating a chart | Charts and Charting in Excel | |||
creating chart with 2 stacked columns and 2 lines | Charts and Charting in Excel | |||
Creating a chart with values from two columns | Charts and Charting in Excel |