If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Variable series length/range
Thanks for the help with adding new series with a macro. The procedure from
the Quick Chart VBA site worked great. Is there some other way to specify the range of cells used in a series? As I add rows of data, I would like to group some series together (based on date data was taken, etc). Below is what I'm currently trying. I wanted to use a variable for the number of rows selected for the data series. Substituting Cells( , ) for Range(" ") has worked for me in the past for general things like Copy and Paste, but it produces an error when I use it after ActiveSheet. Sheets("Summary").Select same_cal = 1 10 If Range("B5").Value = Cells(5 + same_cal, 2).Value Then Cells(5 + same_cal, 2).Select Selection.ClearContents same_cal = same_cal + 1 GoTo 10 End If If same_cal 1 Then ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.SeriesCollection.NewSeries .Name = ActiveSheet.Cells(5, 2) -- vs. Range("B5") .Values = ActiveSheet.Range(Cells(5, 5), Cells(5 + same_cal, 5)) --vs. Range("E5:??") .XValues = ActiveSheet.Range(Cells(5, 1), Cells(5 + same_cal, 1)) --vs. Range("A5:??") End With ElseIf same_cal = 1 Then ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.SeriesCollection.NewSeries .Name = ActiveSheet.Range("B5") .Values = ActiveSheet.Range("E5") .XValues = ActiveSheet.Range("A5") End With End If |
#2
|
|||
|
|||
Variable series length/range
1. You could make non-VBA dynamic charts:
http://peltiertech.com/Excel/Charts/Dynamics.html 2. You could replace Range("B5") with Range("B5:B10"). If you don't know the last row yet, write code that figures it out, and use Range("B5:B" & CStr(iLastRow)). Or use Range("B5").Resize(10) or Cells(5, 2).Resize(iNumberOfRows) or any combination of these. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "JessK" wrote in message ... Thanks for the help with adding new series with a macro. The procedure from the Quick Chart VBA site worked great. Is there some other way to specify the range of cells used in a series? As I add rows of data, I would like to group some series together (based on date data was taken, etc). Below is what I'm currently trying. I wanted to use a variable for the number of rows selected for the data series. Substituting Cells( , ) for Range(" ") has worked for me in the past for general things like Copy and Paste, but it produces an error when I use it after ActiveSheet. Sheets("Summary").Select same_cal = 1 10 If Range("B5").Value = Cells(5 + same_cal, 2).Value Then Cells(5 + same_cal, 2).Select Selection.ClearContents same_cal = same_cal + 1 GoTo 10 End If If same_cal 1 Then ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.SeriesCollection.NewSeries .Name = ActiveSheet.Cells(5, 2) -- vs. Range("B5") .Values = ActiveSheet.Range(Cells(5, 5), Cells(5 + same_cal, 5)) --vs. Range("E5:??") .XValues = ActiveSheet.Range(Cells(5, 1), Cells(5 + same_cal, 1)) --vs. Range("A5:??") End With ElseIf same_cal = 1 Then ActiveSheet.ChartObjects("Chart 1").Activate With ActiveChart.SeriesCollection.NewSeries .Name = ActiveSheet.Range("B5") .Values = ActiveSheet.Range("E5") .XValues = ActiveSheet.Range("A5") End With End If |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can I add an average series to a chart with 2 or more series? | Yaniv | Charts and Charting | 4 | June 16th, 2005 11:37 PM |
SHARED SOLUTION: Horizontal lines on XY Scatter w/multiple series | LeAnne | Charts and Charting | 1 | November 15th, 2004 02:35 PM |
What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula? | SidBord | Charts and Charting | 2 | June 23rd, 2004 07:26 PM |
Hiding some labels on value axes; Changing series names | Leslie | Charts and Charting | 3 | April 28th, 2004 01:21 PM |
Changing a series' Name, X- and Y-values by using its series number | L Mehl | Charts and Charting | 4 | February 4th, 2004 01:32 PM |