| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
iterate through chart series collection
sorry to be bugging the group so mych about charts but Im getting my
butt kicked. The new problem is this: Building a column chart and needing to add the series x axis labels which are set in VBA with ..SeriesCollection(#).XValues for each series. I need to set them all to a named range(the same range of cells) Im working with: some code removed for clearity) Dim aChart As Chart Dim shtNm As String shtNm = ActiveSheet.Name Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) Dim xSer As SeriesCollection For Each xSer In aChart .XValues = Range("code") Next I can set each series individuly but I need to set them as one because the number of series is variable and can change each time the macro runs. The named range "CODE" is always the correct size to match the number in the series. is there a way to make this work? Thanks Robert |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
You need to declare your variable As Series, not As SeriesCollection, and
loop through each series in the chart's SeriesCollection: Dim xSer As Series For Each xSer In aChart.SeriesCollection - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Robert H" wrote in message ps.com... sorry to be bugging the group so mych about charts but Im getting my butt kicked. The new problem is this: Building a column chart and needing to add the series x axis labels which are set in VBA with .SeriesCollection(#).XValues for each series. I need to set them all to a named range(the same range of cells) Im working with: some code removed for clearity) Dim aChart As Chart Dim shtNm As String shtNm = ActiveSheet.Name Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) Dim xSer As SeriesCollection For Each xSer In aChart .XValues = Range("code") Next I can set each series individuly but I need to set them as one because the number of series is variable and can change each time the macro runs. The named range "CODE" is always the correct size to match the number in the series. is there a way to make this work? Thanks Robert |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
I believe you can account for the number of series being dynamic by doing
something like this: Dim xSer As SeriesCollection Dim i As Integer For Each xSer In aChart For i = 1 To xSer.Count xSer.Item(i).XValues = Range("code") Next i Next "Robert H" wrote: sorry to be bugging the group so mych about charts but Im getting my butt kicked. The new problem is this: Building a column chart and needing to add the series x axis labels which are set in VBA with ..SeriesCollection(#).XValues for each series. I need to set them all to a named range(the same range of cells) Im working with: some code removed for clearity) Dim aChart As Chart Dim shtNm As String shtNm = ActiveSheet.Name Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) Dim xSer As SeriesCollection For Each xSer In aChart .XValues = Range("code") Next I can set each series individuly but I need to set them as one because the number of series is variable and can change each time the macro runs. The named range "CODE" is always the correct size to match the number in the series. is there a way to make this work? Thanks Robert |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
Jon I modified my code as such:
Dim xSer As Series For Each xSer In aChart.SeriesCollection xSer.XValues = Range("code") Next this runs without error but the instead of each column having the corresponding "code" underneath it as a label, only the first label is present, centered under the chart. the Code named range is: A!$A$2:$A$8 and contains: Module 01 Module 02 Module 03 Module 14 Module 15 Module 28 Module 34 The resulting Category (X) axis Labels value is =A!$A$2:$A$8 for each series. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
Thanks Vergel.
at this line: For i = 1 To xSer.Count I get "object doesn't support this property or method On Feb 23, 5:37 pm, Vergel Adriano wrote: I believe you can account for the number of series being dynamic by doing something like this: Dim xSer As SeriesCollection Dim i As Integer For Each xSer In aChart For i = 1 To xSer.Count xSer.Item(i).XValues = Range("code") Next i Next "Robert H" wrote: sorry to be bugging the group so mych about charts but Im getting my butt kicked. The new problem is this: Building a column chart and needing to add the series x axis labels which are set in VBA with ..SeriesCollection(#).XValues for each series. I need to set them all to a named range(the same range of cells) Im working with: some code removed for clearity) Dim aChart As Chart Dim shtNm As String shtNm = ActiveSheet.Name Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) Dim xSer As SeriesCollection For Each xSer In aChart .XValues = Range("code") Next I can set each series individuly but I need to set them as one because the number of series is variable and can change each time the macro runs. The named range "CODE" is always the correct size to match the number in the series. is there a way to make this work? Thanks Robert |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
Sorry, the error occurs at the line:
"For Each xSer In aChart" not at the count line |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
Ive tried a few variations and have the same output on the chart but
get some interesting results watching the code In the following the actual range is inserted in the series formula and each series sees the full xvalue range not just its corresponding value. As previously described, only the first label is present, centered under the chart. Dim xSer As Object For Each xSer In aChart.SeriesCollection Debug.Print "befor " & xSer.Formula xSer.XValues = Range("code") Debug.Print "After " & xSer.Formula Next output from DebugPrint befor =SERIES(A!$A$2,,A!$G$2,1) After =SERIES(A!$A$2,A!$A$2:$A$8,A!$G$2,1) befor =SERIES(A!$A$3,,A!$G$3,2) After =SERIES(A!$A$3,A!$A$2:$A$8,A!$G$3,2) befor =SERIES(A!$A$4,,A!$G$4,3) After =SERIES(A!$A$4,A!$A$2:$A$8,A!$G$4,3) befor =SERIES(A!$A$5,,A!$G$5,4) After =SERIES(A!$A$5,A!$A$2:$A$8,A!$G$5,4) befor =SERIES(A!$A$6,,A!$G$6,5) After =SERIES(A!$A$6,A!$A$2:$A$8,A!$G$6,5) befor =SERIES(A!$A$7,,A!$G$7,6) After =SERIES(A!$A$7,A!$A$2:$A$8,A!$G$7,6) befor =SERIES(A!$A$8,,A!$G$8,7) After =SERIES(A!$A$8,A!$A$2:$A$8,A!$G$8,7) The next option was to use the xSer.Name instead of the CODE range because I noticed that the values were the same. as you can see the "after" - debug clearly shows a different value in the xvalue portion of the formula for each series. It just so happens, these are the correct values. Although I would prefer it the be individual ranges rather than the values I could use this for the moment. however, when I looked at the chart, I had the same results!!!!!!!!! only the first label is present, centered under the chart. The resulting Category (X) axis Label values are all {"Module 01"} even thought the xSer formula leads me to think they should be different. Dim xSer As Object For Each xSer In aChart.SeriesCollection Debug.Print "befor " & xSer.Formula xSer.XValues = xSer.Name Debug.Print "After " & xSer.Formula Next befor =SERIES(A!$A$2,,A!$G$2,1) After =SERIES(A!$A$2,{"Module 01"},A!$G$2,1) befor =SERIES(A!$A$3,,A!$G$3,2) After =SERIES(A!$A$3,{"Module 02"},A!$G$3,2) befor =SERIES(A!$A$4,,A!$G$4,3) After =SERIES(A!$A$4,{"Module 03"},A!$G$4,3) befor =SERIES(A!$A$5,,A!$G$5,4) After =SERIES(A!$A$5,{"Module 14"},A!$G$5,4) befor =SERIES(A!$A$6,,A!$G$6,5) After =SERIES(A!$A$6,{"Module 15"},A!$G$6,5) befor =SERIES(A!$A$7,,A!$G$7,6) After =SERIES(A!$A$7,{"Module 28"},A!$G$7,6) befor =SERIES(A!$A$8,,A!$G$8,7) After =SERIES(A!$A$8,{"Module 34"},A!$G$8,7) Stumped again! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
No mystery:
=SERIES(A!$A$6,A!$A$2:$A$8,A!$G$6,5) You have seven categories but only one value, so Excel will use the first value, and therefore only the first category. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Robert H" wrote in message ps.com... Ive tried a few variations and have the same output on the chart but get some interesting results watching the code In the following the actual range is inserted in the series formula and each series sees the full xvalue range not just its corresponding value. As previously described, only the first label is present, centered under the chart. Dim xSer As Object For Each xSer In aChart.SeriesCollection Debug.Print "befor " & xSer.Formula xSer.XValues = Range("code") Debug.Print "After " & xSer.Formula Next output from DebugPrint befor =SERIES(A!$A$2,,A!$G$2,1) After =SERIES(A!$A$2,A!$A$2:$A$8,A!$G$2,1) befor =SERIES(A!$A$3,,A!$G$3,2) After =SERIES(A!$A$3,A!$A$2:$A$8,A!$G$3,2) befor =SERIES(A!$A$4,,A!$G$4,3) After =SERIES(A!$A$4,A!$A$2:$A$8,A!$G$4,3) befor =SERIES(A!$A$5,,A!$G$5,4) After =SERIES(A!$A$5,A!$A$2:$A$8,A!$G$5,4) befor =SERIES(A!$A$6,,A!$G$6,5) After =SERIES(A!$A$6,A!$A$2:$A$8,A!$G$6,5) befor =SERIES(A!$A$7,,A!$G$7,6) After =SERIES(A!$A$7,A!$A$2:$A$8,A!$G$7,6) befor =SERIES(A!$A$8,,A!$G$8,7) After =SERIES(A!$A$8,A!$A$2:$A$8,A!$G$8,7) The next option was to use the xSer.Name instead of the CODE range because I noticed that the values were the same. as you can see the "after" - debug clearly shows a different value in the xvalue portion of the formula for each series. It just so happens, these are the correct values. Although I would prefer it the be individual ranges rather than the values I could use this for the moment. however, when I looked at the chart, I had the same results!!!!!!!!! only the first label is present, centered under the chart. The resulting Category (X) axis Label values are all {"Module 01"} even thought the xSer formula leads me to think they should be different. Dim xSer As Object For Each xSer In aChart.SeriesCollection Debug.Print "befor " & xSer.Formula xSer.XValues = xSer.Name Debug.Print "After " & xSer.Formula Next befor =SERIES(A!$A$2,,A!$G$2,1) After =SERIES(A!$A$2,{"Module 01"},A!$G$2,1) befor =SERIES(A!$A$3,,A!$G$3,2) After =SERIES(A!$A$3,{"Module 02"},A!$G$3,2) befor =SERIES(A!$A$4,,A!$G$4,3) After =SERIES(A!$A$4,{"Module 03"},A!$G$4,3) befor =SERIES(A!$A$5,,A!$G$5,4) After =SERIES(A!$A$5,{"Module 14"},A!$G$5,4) befor =SERIES(A!$A$6,,A!$G$6,5) After =SERIES(A!$A$6,{"Module 15"},A!$G$6,5) befor =SERIES(A!$A$7,,A!$G$7,6) After =SERIES(A!$A$7,{"Module 28"},A!$G$7,6) befor =SERIES(A!$A$8,,A!$G$8,7) After =SERIES(A!$A$8,{"Module 34"},A!$G$8,7) Stumped again! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
iterate through chart series collection
A series has no Count property, but the SeriesCollection does. You need
either Dim xSer As Series For Each xSer In aChart.SeriesCollection ' blah Next which I suggested, or Dim i As Integer For i = 1 to aChart.SeriesCollection.Count ' blah Next - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Robert H" wrote in message oups.com... Thanks Vergel. at this line: For i = 1 To xSer.Count I get "object doesn't support this property or method On Feb 23, 5:37 pm, Vergel Adriano wrote: I believe you can account for the number of series being dynamic by doing something like this: Dim xSer As SeriesCollection Dim i As Integer For Each xSer In aChart For i = 1 To xSer.Count xSer.Item(i).XValues = Range("code") Next i Next "Robert H" wrote: sorry to be bugging the group so mych about charts but Im getting my butt kicked. The new problem is this: Building a column chart and needing to add the series x axis labels which are set in VBA with ..SeriesCollection(#).XValues for each series. I need to set them all to a named range(the same range of cells) Im working with: some code removed for clearity) Dim aChart As Chart Dim shtNm As String shtNm = ActiveSheet.Name Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) Dim xSer As SeriesCollection For Each xSer In aChart .XValues = Range("code") Next I can set each series individuly but I need to set them as one because the number of series is variable and can change each time the macro runs. The named range "CODE" is always the correct size to match the number in the series. is there a way to make this work? Thanks Robert |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Series Collection Name Problem | Excel Programming | |||
| Series collection pb on Ecxel 2003 chart | Excel Programming | |||
| Use "For... Each" to Iterate Through a Cells Collection? | Excel Programming | |||
| Series Collection Points | Excel Programming | |||
| Iterate over Collection Objects in Container. | Excel Programming | |||
Linear Mode
