I want to draw/amend a chart, but VBA stumbles over the range(cells)
bit, any ideas why?? Player "you" has worksheet 3 and the other
players have the consecutive worksheets.

Dirk

Sub Macro6()
Dim players, round, i
players = 2
round = 10
Charts(1).Activate
ActiveChart.ChartType = xlLineMarkers
' ActiveChart.SeriesCollection(1).XValues =
Worksheets(3).Range(Cells(2, 1), Cells(round + 1, 1))
ActiveChart.SeriesCollection(1).Values =
Worksheets(3).Range(Cells(2, 1), Cells(round + 1, 1))
ActiveChart.SeriesCollection(1).Name = "You"
For i = 2 To players + 3
ActiveChart.SeriesCollection(i).Values = Worksheets(i +
2).Range(Cells(2, 1), Cells(round + 1, 1))
ActiveChart.SeriesCollection(i).Name = "Player" & i
Next i
End Sub

Describe "stumbles". What's the error message (not the error number, because
it's probably 1004).
What is the chart type? Do the intended ranges contain data? Do the series
plot correctly before running the macro?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dirk Nachbar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I want to draw/amend a chart, but VBA stumbles over the range(cells)
> bit, any ideas why?? Player "you" has worksheet 3 and the other
> players have the consecutive worksheets.
>
> Dirk
>
> Sub Macro6()
> Dim players, round, i
> players = 2
> round = 10
> Charts(1).Activate
> ActiveChart.ChartType = xlLineMarkers
> ' ActiveChart.SeriesCollection(1).XValues =
> Worksheets(3).Range(Cells(2, 1), Cells(round + 1, 1))
> ActiveChart.SeriesCollection(1).Values =
> Worksheets(3).Range(Cells(2, 1), Cells(round + 1, 1))
> ActiveChart.SeriesCollection(1).Name = "You"
> For i = 2 To players + 3
> ActiveChart.SeriesCollection(i).Values = Worksheets(i +
> 2).Range(Cells(2, 1), Cells(round + 1, 1))
> ActiveChart.SeriesCollection(i).Name = "Player" & i
> Next i
> End Sub
>

Dirk,

"Cells" without a qualifier refers to the active sheet.
Note the dots below...

With Worksheets(3)
..Range(.Cells(2, 1), .Cells(round + 1, 1))
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Dirk Nachbar" <(E-Mail Removed)>
wrote in message
I want to draw/amend a chart, but VBA stumbles over the range(cells)
bit, any ideas why?? Player "you" has worksheet 3 and the other
players have the consecutive worksheets.
Dirk

Sub Macro6()
Dim players, round, i
players = 2
round = 10
Charts(1).Activate
ActiveChart.ChartType = xlLineMarkers
' ActiveChart.SeriesCollection(1).XValues =
Worksheets(3).Range(Cells(2, 1), Cells(round + 1, 1))
ActiveChart.SeriesCollection(1).Values =
Worksheets(3).Range(Cells(2, 1), Cells(round + 1, 1))
ActiveChart.SeriesCollection(1).Name = "You"
For i = 2 To players + 3
ActiveChart.SeriesCollection(i).Values = Worksheets(i +
2).Range(Cells(2, 1), Cells(round + 1, 1))
ActiveChart.SeriesCollection(i).Name = "Player" & i
Next i
End Sub