Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
I am getting the following error for executing this code
Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Straight from VBA help file:
Charts("chart1").SeriesCollection.Add _ source:=Worksheets("sheet1").Range("a1:a19") " wrote: I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Sorry, I didn't read far enough. Stand by.
" wrote: I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
JLGWhiz, I am unable to succeed in adding series from the code given
by you. I get "Subscript out of range error". Can you help please? Thanks On Oct 18, 8:12 pm, JLGWhiz wrote: Straight from VBA help file: Charts("chart1").SeriesCollection.Add _ source:=Worksheets("sheet1").Range("a1:a19") " wrote: I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Well, I tried to find something that was directly related to what you are
attempting and did not do very well at it. However, I did find a site that gives a lot of references for charting and using VBA to do so. Here is the site address: http://www.contextures.com/charts.html " wrote: I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Hi. Are you using Excel 2007?
The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message ups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
On Oct 19, 7:19 am, JLGWhiz wrote:
Well, I tried to find something that was directly related to what you are attempting and did not do very well at it. However, I did find a site that gives a lot of references for charting and using VBA to do so. Here is the site address: http://www.contextures.com/charts.html " wrote: I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Thanks. I fixing most of my part but still facing errors in this part mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" R(j)C1:R(k)C1 and R(j)C2 declaration are ok? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
On Oct 19, 7:51 am, "Dana DeLouis" wrote:
Hi. Are you using Excel 2007? The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message ups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Dana, I will get it a try.. I appreciate your help. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
On Oct 19, 7:51 am, "Dana DeLouis" wrote:
Hi. Are you using Excel 2007? The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message ups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Dana, with your for loop, how to modify these two lines in the code.. rest seems ok for me. First line, i have j and k and second line I have j. Please let me know if will your code fit into mine? Kind of spilliting my hair here! mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" I think someone else is on the boat. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Hi. Not sure, but does this general idea work?
Sub Demo() Dim R As Long ' (R)ow Dim strAddress As String ActiveSheet.ChartObjects("Chart 1").Activate For R = 1618 To 124234 Step 524 strAddress = Cells(R, 1).Resize(524, 1).Address(False, False) ActiveChart.SeriesCollection.Add Range(strAddress) Next R End Sub I think you are asking about putting Labels on the Series Data. I'm stuck on that myself. Things are harder to read with Excel's 2007 poor help system. -- Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... On Oct 19, 7:51 am, "Dana DeLouis" wrote: Hi. Are you using Excel 2007? The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message ups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Dana, with your for loop, how to modify these two lines in the code.. rest seems ok for me. First line, i have j and k and second line I have j. Please let me know if will your code fit into mine? Kind of spilliting my hair here! mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" I think someone else is on the boat. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Dana, I will give this a try. I am using Excel 2003.
All I am trying to do is trying to loop the the series given below. I have 240 series in my excel sheet but I have given the code for only 3 below.. Writing everything manually is a nightmare. As you could see, the Xvalues are the same. Y values differ for each series with 524 interval. ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(1).Values = "='Sheet1'!R46C1:R569C1" ActiveChart.SeriesCollection(1).Name = "='Sheet1'!R46C2" ActiveChart.SeriesCollection(2).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(2).Values = "='Sheet1'! R570C1:R1093C1" ActiveChart.SeriesCollection(2).Name = "='Sheet1'!R570C2" ActiveChart.SeriesCollection(3).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(3).Values = "='Sheet1'!R1094:R1617C1" ActiveChart.SeriesCollection(3).Name = "='Sheet1'!R1094C2" ................... That is the reason I had something like For i = 0 to 15 j = (i * 524)+46 k = j + 523 ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" ActiveChart.SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next I know there is something wrong with my "FOR" loop.. If that is fixed, I think I will be safe. Can you please help fix the for loop for Y values and the legend name? As I said earlier, X values are constant for all the 240 series. Thanks On Oct 19, 9:20 am, "Dana DeLouis" wrote: Hi. Not sure, but does this general idea work? Sub Demo() Dim R As Long ' (R)ow Dim strAddress As String ActiveSheet.ChartObjects("Chart 1").Activate For R = 1618 To 124234 Step 524 strAddress = Cells(R, 1).Resize(524, 1).Address(False, False) ActiveChart.SeriesCollection.Add Range(strAddress) Next R End Sub I think you are asking about putting Labels on the Series Data. I'm stuck on that myself. Things are harder to read with Excel's 2007 poor help system. -- Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... On Oct 19, 7:51 am, "Dana DeLouis" wrote: Hi. Are you using Excel 2007? The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message roups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Dana, with your for loop, how to modify these two lines in the code.. rest seems ok for me. First line, i have j and k and second line I have j. Please let me know if will your code fit into mine? Kind of spilliting my hair here! mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" I think someone else is on the boat.- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
Hi. I'm not a real expert here, but this small test seemed to work.
If you can, I'd try it with your sheet in A1 notation, and not R1C1 notation. You may need an X-Y scatter chart, but I'm not sure. Dim ns As Series Set ns = ActiveChart.SeriesCollection.NewSeries ns.XValues = Range("A1:A10") ns.Values = Range("B1:B10") ns.Name = "Test" Don't know what error you are receiving, but my guess might be that when you do: ..SeriesCollection(1)... Series #1 might not exists yet. Anything here getting you closer? -- Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... Dana, I will give this a try. I am using Excel 2003. All I am trying to do is trying to loop the the series given below. I have 240 series in my excel sheet but I have given the code for only 3 below.. Writing everything manually is a nightmare. As you could see, the Xvalues are the same. Y values differ for each series with 524 interval. ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(1).Values = "='Sheet1'!R46C1:R569C1" ActiveChart.SeriesCollection(1).Name = "='Sheet1'!R46C2" ActiveChart.SeriesCollection(2).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(2).Values = "='Sheet1'! R570C1:R1093C1" ActiveChart.SeriesCollection(2).Name = "='Sheet1'!R570C2" ActiveChart.SeriesCollection(3).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(3).Values = "='Sheet1'!R1094:R1617C1" ActiveChart.SeriesCollection(3).Name = "='Sheet1'!R1094C2" .................. That is the reason I had something like For i = 0 to 15 j = (i * 524)+46 k = j + 523 ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" ActiveChart.SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next I know there is something wrong with my "FOR" loop.. If that is fixed, I think I will be safe. Can you please help fix the for loop for Y values and the legend name? As I said earlier, X values are constant for all the 240 series. Thanks On Oct 19, 9:20 am, "Dana DeLouis" wrote: Hi. Not sure, but does this general idea work? Sub Demo() Dim R As Long ' (R)ow Dim strAddress As String ActiveSheet.ChartObjects("Chart 1").Activate For R = 1618 To 124234 Step 524 strAddress = Cells(R, 1).Resize(524, 1).Address(False, False) ActiveChart.SeriesCollection.Add Range(strAddress) Next R End Sub I think you are asking about putting Labels on the Series Data. I'm stuck on that myself. Things are harder to read with Excel's 2007 poor help system. -- Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... On Oct 19, 7:51 am, "Dana DeLouis" wrote: Hi. Are you using Excel 2007? The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message roups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Dana, with your for loop, how to modify these two lines in the code.. rest seems ok for me. First line, i have j and k and second line I have j. Please let me know if will your code fit into mine? Kind of spilliting my hair here! mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" I think someone else is on the boat.- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add series to a chart using a macro
On Oct 19, 11:19 am, "Dana DeLouis" wrote:
Hi. I'm not a real expert here, but this small test seemed to work. If you can, I'd try it with your sheet in A1 notation, and not R1C1 notation. You may need an X-Y scatter chart, but I'm not sure. Dim ns As Series Set ns = ActiveChart.SeriesCollection.NewSeries ns.XValues = Range("A1:A10") ns.Values = Range("B1:B10") ns.Name = "Test" Don't know what error you are receiving, but my guess might be that when you do: .SeriesCollection(1)... Series #1 might not exists yet. Anything here getting you closer? -- Dana DeLouis Windows XP & Excel 2007 wrote in message oups.com... Dana, I will give this a try. I am using Excel 2003. All I am trying to do is trying to loop the the series given below. I have 240 series in my excel sheet but I have given the code for only 3 below.. Writing everything manually is a nightmare. As you could see, the Xvalues are the same. Y values differ for each series with 524 interval. ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(1).Values = "='Sheet1'!R46C1:R569C1" ActiveChart.SeriesCollection(1).Name = "='Sheet1'!R46C2" ActiveChart.SeriesCollection(2).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(2).Values = "='Sheet1'! R570C1:R1093C1" ActiveChart.SeriesCollection(2).Name = "='Sheet1'!R570C2" ActiveChart.SeriesCollection(3).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(3).Values = "='Sheet1'!R1094:R1617C1" ActiveChart.SeriesCollection(3).Name = "='Sheet1'!R1094C2" .................. That is the reason I had something like For i = 0 to 15 j = (i * 524)+46 k = j + 523 ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" ActiveChart.SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" ActiveChart.SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next I know there is something wrong with my "FOR" loop.. If that is fixed, I think I will be safe. Can you please help fix the for loop for Y values and the legend name? As I said earlier, X values are constant for all the 240 series. Thanks On Oct 19, 9:20 am, "Dana DeLouis" wrote: Hi. Not sure, but does this general idea work? Sub Demo() Dim R As Long ' (R)ow Dim strAddress As String ActiveSheet.ChartObjects("Chart 1").Activate For R = 1618 To 124234 Step 524 strAddress = Cells(R, 1).Resize(524, 1).Address(False, False) ActiveChart.SeriesCollection.Add Range(strAddress) Next R End Sub I think you are asking about putting Labels on the Series Data. I'm stuck on that myself. Things are harder to read with Excel's 2007 poor help system. -- Dana DeLouis Windows XP & Excel 2007 wrote in message groups.com... On Oct 19, 7:51 am, "Dana DeLouis" wrote: Hi. Are you using Excel 2007? The last three loops give Rows above 64k. Sub test() For i = 238 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Debug.Print j; k Next End Sub 123186 123709 123710 124233 124234 124757 If I did this correctly, maybe: For TopRow = 1618 To 124234 Step 524 BottomRow = Toptow + 523 Your code part: "='Sheet1'!R(j)C1:R(k)C1" Here, j & k are part of a string. Vba doesn't know to replace "j" with the value j. Maybe you could work something like this idea into your code: For r = 1618 To 124234 Step 524 StrAddress = Cells(r, 1).Resize(524, 1).Address(False, False) ' Do Stuff... Next r -- Dana DeLouis Windows XP & Excel 2007 wrote in message roups.com... I am getting the following error for executing this code Unable to get the chart object property of the worksheet class Please help me solve the problem. I am trying to add 240 series to a chart. If someone can help to add 10 series then I will tweak the code for 240 series. Please help. Code: Dim mySeries As Series For i = 6 To 240 j = ((i - 3) * 524) + 46 k = j + 523 Set mySeries = Sheets("Sheet1").ChartObjects("Chart1"). _ Chart.SeriesCollection.NewSeries mySeries.SeriesCollection(i).XValues = "='Sheet1'!R46C3:R569C3" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" Next Dana, with your for loop, how to modify these two lines in the code.. rest seems ok for me. First line, i have j and k and second line I have j. Please let me know if will your code fit into mine? Kind of spilliting my hair here! mySeries.ChartObjects("Chart 1").SeriesCollection(i).Values = "='Sheet1'!R(j)C1:R(k)C1" mySeries.ChartObjects("Chart 1").SeriesCollection(i).Name = "='Sheet1'!R(j)C2" I think someone else is on the boat.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - not closer.. still stuck.. :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to change series xvalues in chart | Excel Programming | |||
Expand Chart Macro to Plot Multiple Series | Excel Programming | |||
Use a macro to expand data series for chart? | Charts and Charting in Excel | |||
Adding data series to chart via macro | Charts and Charting in Excel | |||
how to turn of a series in data sheet of a power point chart using macro | Excel Programming |