One line query on for loop assignment

[ucanalways]

I have a line
ActiveChart.SeriesCollection(i).Name = Sheets("Sheet1").Cells(j, 2)
for assigning the legend name in a scatter chart which works
correctly.

I would like to assign .values from cells(j,1) to cells(k,1).. What is
the correct syntax to do it?
I get an error when I have a line like this.

ActiveChart.SeriesCollection(i).Values = Sheets("Sheet1").Cells(j,
1):cells(k,1)

Thanks

 

[Cringing Dragon]

Encase it in a Range, as follows:
Sheets("Sheet1").Range(Cells(j,1),Cells(k,1))

 

[ucanalways]

Encase it in a Range, as follows:
Sheets("Sheet1").Range(Cells(j,1),Cells(k,1))








- Show quoted text -

Thanks. But, I get an error like this

Method 'cells' of object '_Global' failed.

Please help me fix the error.

 

[Cringing Dragon]

How did you apply it? I didn't show the whole line, just the bit of it that I
was suggesting you change. The whole line needs to be:
ActiveChart.SeriesCollection(i).Values =
Sheets("Sheet1").Range(Cells(j,1),Cells(k,1))

In this post, that will wrap around onto 2 lines. You need to put it all on
one line.

If that's what you did already, or if you still get the error, can you
please post the block of lines around that one (ie the line before, that
line, and the line after)? Copy and paste them straight from your code, so I
can see exactly what you have.

 

[ucanalways]

How did you apply it? I didn't show the whole line, just the bit of it that I
was suggesting you change. The whole line needs to be:
ActiveChart.SeriesCollection(i).Values =
Sheets("Sheet1").Range(Cells(j,1),Cells(k,1))

In this post, that will wrap around onto 2 lines. You need to put it all on
one line.

If that's what you did already, or if you still get the error, can you
please post the block of lines around that one (ie the line before, that
line, and the line after)? Copy and paste them straight from your code, so I
can see exactly what you have.








- Show quoted text -

Hi.. ya I did as u said i.e. had it in a single line. The code is
given below

Charts.Add
ActiveChart.ChartType = xlXYScatter

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 =
Sheets("Sheet1").Range(Cells(j, 1), Cells(k, 1)) ' ****Error here
ActiveChart.SeriesCollection(i).Name = Sheets("Sheet1").Cells(j, 2)

Next

End Sub

I hope to see ur reply when I wake up tomorrow morning. Thank you very
much.

 

[Cringing Dragon]

Sorry, yes I forgot to put the sheet reference in front of the cells.
Sheets("Sheet1").Range(Sheets("Sheet1").Cells(j,1),Sheets("Sheet1").Cells(k,1))

Give that a shot.

 

[Tom Ogilvy]

If sheet1 isn't the active sheet or the code is in a sheet module other than
sheet1, then qualify all references like this:

With Sheets("Sheet1")
ActiveChart.SeriesCollection(i).Values = _
.Range(.Cells(j,1),.Cells(k,1))
End With

 

[ucanalways]

If sheet1 isn't the active sheet or the code is in a sheet module other than
sheet1, then qualify all references like this:

With Sheets("Sheet1")
ActiveChart.SeriesCollection(i).Values = _
.Range(.Cells(j,1),.Cells(k,1))
End With

--
Regards,
Tom Ogilvy











- Show quoted text -

Tom, your code works perfectly. Thanks a lot.. Can you please suggest
me a book that can train me with VBA (coding) for charts? Thanks..

Cringing Dragon, I used your code but I got an error saying "Subscript
out of range". I mark Tom's code as the best fit for this thread.