How to add series to a chart using a macro

U

ucanalways

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
 
J

JLGWhiz

Straight from VBA help file:

Charts("chart1").SeriesCollection.Add _
source:=Worksheets("sheet1").Range("a1:a19")
 
U

ucanalways

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
 
J

JLGWhiz

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
 
D

Dana DeLouis

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"
Click to expand...

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
 
T

Thulasiram

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
Click to expand...

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?
 
U

ucanalways

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:


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
Click to expand...

Dana, I will get it a try.. I appreciate your help.
 
U

ucanalways

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:


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
Click to expand...

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.
 
D

Dana DeLouis

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.
 
U

ucanalways

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
 
D

Dana DeLouis

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?
 
U

ucanalways

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












- Show quoted text -
Click to expand...

not closer.. still stuck.. :(
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FOR loop macro 5
FOR loop macro clarification 3
Need help to read Pie Chart Series Range 0
Chart - Variable Series Collection 3
Putting the series 1 and series 2 titles from a worksheet 0
How to Expose SeriesCollection? 0
Bubble Chart 1
linechart through vb script 0

Top