Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Continuity of the series
Hi,
I have a series in the graph, a column B starting from row 1 to 20. These values are populated from other source and not always there will be value in each cell, however the continuity of the graph should be kept. Because of the no values in the cell the series graph is incomplete. CAn we programatically complete the series? Suppose this the the series: Col B 90.00 95.00 90.00 95.00 90.00 10.00 25.00 70.00 80.00 90.00 90.00 98.00 End The series in the graph is getting truncated after first break in the values. Pls help me. ******************* Sample code: Sub GenerateGraph() Dim NewSet As String Dim NewSet1 As String Dim CurLocation As String CurLocation = ActiveCell.Address NewSet = "B2:" & Range("B2").End(xlDown).Address NewSet1 = "C2:" & Range("C2").End(xlDown).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData _ Source:=Union(Sheets(ActiveSheet.Name).Range(NewSe t), _ Sheets(ActiveSheet.Name).Range(NewSet1)) Range(CurLocation).Select End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Continuity of the series
Hi Joe,
A. If your series always stays in the cells B1:C20, use the following: ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range("B2:C20") B. If you always want to include 20 entries in the series, but don't know in advance where the series will be populated, use the following: NewSet = Range(ActiveCell.Address, ActiveCell.Offset(19, 1).Address).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range(NewSet) C. If your series goes from a current cell down to the cell one above and one to the right of "End", use the following: Dim c As Range Set c = Cells.Find(What:="End", LookIn:=xlValues, lookat:=xlWhole) NewSet = Range(ActiveCell.Address, c.Offset(-1, 1).Address).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range(NewSet) Hope it helps, Ilya "Joe" wrote: Hi, I have a series in the graph, a column B starting from row 1 to 20. These values are populated from other source and not always there will be value in each cell, however the continuity of the graph should be kept. Because of the no values in the cell the series graph is incomplete. CAn we programatically complete the series? Suppose this the the series: Col B 90.00 95.00 90.00 95.00 90.00 10.00 25.00 70.00 80.00 90.00 90.00 98.00 End The series in the graph is getting truncated after first break in the values. Pls help me. ******************* Sample code: Sub GenerateGraph() Dim NewSet As String Dim NewSet1 As String Dim CurLocation As String CurLocation = ActiveCell.Address NewSet = "B2:" & Range("B2").End(xlDown).Address NewSet1 = "C2:" & Range("C2").End(xlDown).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData _ Source:=Union(Sheets(ActiveSheet.Name).Range(NewSe t), _ Sheets(ActiveSheet.Name).Range(NewSet1)) Range(CurLocation).Select End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Continuity of the series
Hi Ilya,
Thanks so much for the solution. My code is like below which is working fine, and I want to add the feature/functionality only for Column G: There are 6 columns in the code: My code: ******************************* Sub GenerateGraph() Dim NewSet As String Dim NewSet1 As String Dim NewSet2 As String Dim NewSet3 As String Dim NewSet4 As String Dim NewSet5 As String Dim CurLocation As String CurLocation = ActiveCell.Address Sheets("Report").Select Columns("N:T").Select Application.CutCopyMode = False Selection.Copy Sheets("Graph").Select Columns("A:G").Select ActiveSheet.Paste NewSet = "B2:" & Range("B2").End(xlDown).Address NewSet1 = "C2:" & Range("C2").End(xlDown).Address NewSet2 = "D2:" & Range("D2").End(xlDown).Address NewSet3 = "E2:" & Range("E2").End(xlDown).Address NewSet4 = "F2:" & Range("F2").End(xlDown).Address NewSet5 = "G2:" & Range("G2").End(xlDown).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData _ Source:=Union(Sheets(ActiveSheet.Name).Range(NewSe t), _ Sheets(ActiveSheet.Name).Range(NewSet1), Sheets(ActiveSheet.Name).Range(NewSet2), Sheets(ActiveSheet.Name).Range(NewSet3), Sheets(ActiveSheet.Name).Range(NewSet4), Sheets(ActiveSheet.Name).Range(NewSet5)) ActiveChart.SeriesCollection(1).Name = Range("Graph!B1") ActiveChart.SeriesCollection(2).Name = Range("Graph!C1") ActiveChart.SeriesCollection(3).Name = Range("Graph!D1") ActiveChart.SeriesCollection(4).Name = Range("Graph!E1") ActiveChart.SeriesCollection(5).Name = Range("Graph!F1") ActiveChart.SeriesCollection(6).Name = Range("Graph!G1") Range(CurLocation).Select End Sub ************************* How do I take care of the broken range becaouse of unavailable data only for Column G? Thanks somuch in advance. Regards "txilya" wrote: Hi Joe, A. If your series always stays in the cells B1:C20, use the following: ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range("B2:C20") B. If you always want to include 20 entries in the series, but don't know in advance where the series will be populated, use the following: NewSet = Range(ActiveCell.Address, ActiveCell.Offset(19, 1).Address).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range(NewSet) C. If your series goes from a current cell down to the cell one above and one to the right of "End", use the following: Dim c As Range Set c = Cells.Find(What:="End", LookIn:=xlValues, lookat:=xlWhole) NewSet = Range(ActiveCell.Address, c.Offset(-1, 1).Address).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range(NewSet) Hope it helps, Ilya "Joe" wrote: Hi, I have a series in the graph, a column B starting from row 1 to 20. These values are populated from other source and not always there will be value in each cell, however the continuity of the graph should be kept. Because of the no values in the cell the series graph is incomplete. CAn we programatically complete the series? Suppose this the the series: Col B 90.00 95.00 90.00 95.00 90.00 10.00 25.00 70.00 80.00 90.00 90.00 98.00 End The series in the graph is getting truncated after first break in the values. Pls help me. ******************* Sample code: Sub GenerateGraph() Dim NewSet As String Dim NewSet1 As String Dim CurLocation As String CurLocation = ActiveCell.Address NewSet = "B2:" & Range("B2").End(xlDown).Address NewSet1 = "C2:" & Range("C2").End(xlDown).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData _ Source:=Union(Sheets(ActiveSheet.Name).Range(NewSe t), _ Sheets(ActiveSheet.Name).Range(NewSet1)) Range(CurLocation).Select End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Continuity of the series
Hi Joe,
If you want to use the same number of entries in your "G" series as in another continues series - "B", use the following: NewSet5 = "G2:G" & Range("B2").End(xlDown).Row If the number of entries in "G" series that you want to display is not related to other series, use the following: NewSet5 = "G2:G" & Range("B65536").End(xlUp).Row This approach assumes max # of rows 65536. This number will increase in Excel 2007. Ilya "Joe" wrote: Hi Ilya, Thanks so much for the solution. My code is like below which is working fine, and I want to add the feature/functionality only for Column G: There are 6 columns in the code: My code: ******************************* Sub GenerateGraph() Dim NewSet As String Dim NewSet1 As String Dim NewSet2 As String Dim NewSet3 As String Dim NewSet4 As String Dim NewSet5 As String Dim CurLocation As String CurLocation = ActiveCell.Address Sheets("Report").Select Columns("N:T").Select Application.CutCopyMode = False Selection.Copy Sheets("Graph").Select Columns("A:G").Select ActiveSheet.Paste NewSet = "B2:" & Range("B2").End(xlDown).Address NewSet1 = "C2:" & Range("C2").End(xlDown).Address NewSet2 = "D2:" & Range("D2").End(xlDown).Address NewSet3 = "E2:" & Range("E2").End(xlDown).Address NewSet4 = "F2:" & Range("F2").End(xlDown).Address NewSet5 = "G2:" & Range("G2").End(xlDown).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData _ Source:=Union(Sheets(ActiveSheet.Name).Range(NewSe t), _ Sheets(ActiveSheet.Name).Range(NewSet1), Sheets(ActiveSheet.Name).Range(NewSet2), Sheets(ActiveSheet.Name).Range(NewSet3), Sheets(ActiveSheet.Name).Range(NewSet4), Sheets(ActiveSheet.Name).Range(NewSet5)) ActiveChart.SeriesCollection(1).Name = Range("Graph!B1") ActiveChart.SeriesCollection(2).Name = Range("Graph!C1") ActiveChart.SeriesCollection(3).Name = Range("Graph!D1") ActiveChart.SeriesCollection(4).Name = Range("Graph!E1") ActiveChart.SeriesCollection(5).Name = Range("Graph!F1") ActiveChart.SeriesCollection(6).Name = Range("Graph!G1") Range(CurLocation).Select End Sub ************************* How do I take care of the broken range becaouse of unavailable data only for Column G? Thanks somuch in advance. Regards "txilya" wrote: Hi Joe, A. If your series always stays in the cells B1:C20, use the following: ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range("B2:C20") B. If you always want to include 20 entries in the series, but don't know in advance where the series will be populated, use the following: NewSet = Range(ActiveCell.Address, ActiveCell.Offset(19, 1).Address).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range(NewSet) C. If your series goes from a current cell down to the cell one above and one to the right of "End", use the following: Dim c As Range Set c = Cells.Find(What:="End", LookIn:=xlValues, lookat:=xlWhole) NewSet = Range(ActiveCell.Address, c.Offset(-1, 1).Address).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.SetSourceData Source:=Range(NewSet) Hope it helps, Ilya "Joe" wrote: Hi, I have a series in the graph, a column B starting from row 1 to 20. These values are populated from other source and not always there will be value in each cell, however the continuity of the graph should be kept. Because of the no values in the cell the series graph is incomplete. CAn we programatically complete the series? Suppose this the the series: Col B 90.00 95.00 90.00 95.00 90.00 10.00 25.00 70.00 80.00 90.00 90.00 98.00 End The series in the graph is getting truncated after first break in the values. Pls help me. ******************* Sample code: Sub GenerateGraph() Dim NewSet As String Dim NewSet1 As String Dim CurLocation As String CurLocation = ActiveCell.Address NewSet = "B2:" & Range("B2").End(xlDown).Address NewSet1 = "C2:" & Range("C2").End(xlDown).Address ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.PlotArea.Select ActiveChart.SetSourceData _ Source:=Union(Sheets(ActiveSheet.Name).Range(NewSe t), _ Sheets(ActiveSheet.Name).Range(NewSet1)) Range(CurLocation).Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart series | Charts and Charting in Excel | |||
Trying to change ColorIndex for series settings | Charts and Charting in Excel | |||
Chart -- Source Data... -- Series dialog window | Charts and Charting in Excel | |||
How to change Series order in a Combination Chart? | Charts and Charting in Excel | |||
series graph -- one series being added to another series | Charts and Charting in Excel |