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 |
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 |
All times are GMT +1. The time now is 10:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com