FOR loop macro clarification

U

ucanalways

I get an error when I execute the for loop. Please help me fix the
error.

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"
' Error here
ActiveChart.SeriesCollection(i).Name = "='Sheet1'!
R(j)C2" 'Error here


Next

Thanks
 
T

Tom Hutchins

Excel can't substitute values for j & K because they are 'hidden' inside
strings. Try

ActiveChart.SeriesCollection(i).Values = "='Sheet1'!R(" & j & ")C1:R(" & k &
")C1"
ActiveChart.SeriesCollection(i).Name = "='Sheet1'!R(" & j & ")C2"

Hope this helps,

Hutch
 
J

JE McGimpsey

It probably would help to say *what* error you get, but one apparent
problems is that the SeriesCollection index starts at 1, not 0, so
SeriesCollection(i) will error out when i = 0
 
U

ucanalways

JE, I agree with you and changed counter starting from 1.. Tom.. I am
using your code..Now, the error I get after implementing Tom's code is

Unable to set the value propoerty of the series class

Can you please help me fix this error? Thanks

The code I use is

sub chart ()

Charts.Add

ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A45:F63000"), PlotBy:= _
xlColumns

For i = 1 To 10
j = ((i - 1) * 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" '''''''''''' Error here
ActiveChart.SeriesCollection(i).Name = "=Sheet1'!R(" & j & ")C2"


Next
 

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

Top