help with VB in excel

[Kevin Long]

Hi everybody. I was wondering if somebody out there could tell me what is
wrong with this code? I'm trying to plot a chart in excel using VB. For
this example, the value of the Index variable is 150, but it could be
anything because it is calculated earlier in the code. I can't see anything
wrong with the code myself, but it seems not to like it. Any help would be
appreciated.

Kevin

Index = 150

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1), Cells(Index
+ 8, 1))
ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2).Value,
Cells(Index + 8, 2))

[Kevin Long]

Actually, here is an update to the code, I made a change. Any help is
appreciated.

Kevin
Index = 150

ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1), Cells(Index +
8, 1))
ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2), Cells(Index + 8,
2))
"Kevin Long" <su*********@hotmail.com> wrote in message
news:Aj*********************@news20.bellglobal.com ...

Hi everybody. I was wondering if somebody out there could tell me what is
wrong with this code? I'm trying to plot a chart in excel using VB. For
this example, the value of the Index variable is 150, but it could be
anything because it is calculated earlier in the code. I can't see
anything wrong with the code myself, but it seems not to like it. Any
help would be appreciated.

Kevin

Index = 150

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1),
Cells(Index + 8, 1))
ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2).Value,
Cells(Index + 8, 2))

[Steve Gerrard]

"Kevin Long" <su*********@hotmail.com> wrote in message
news:mV*********************@news20.bellglobal.com ...
|
| ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1),
Cells(Index +
| 8, 1))
| ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2),
Cells(Index + 8,
| 2))
|
| "Kevin Long" <su*********@hotmail.com> wrote in message
| news:Aj*********************@news20.bellglobal.com ...
| > Hi everybody. I was wondering if somebody out there could tell me
what is
| > wrong with this code? I'm trying to plot a chart in excel using VB.
For
| > this example, the value of the Index variable is 150, but it could
be
| > anything because it is calculated earlier in the code. I can't see
| > anything wrong with the code myself, but it seems not to like it.
Any
| > help would be appreciated.
| >
| > Index = 150
| >
| > Charts.Add
| > ActiveChart.ChartType = xlXYScatterSmooth
| > ActiveChart.SeriesCollection.NewSeries
| >
| > ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1),
| > Cells(Index + 8, 1))
| > ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2).Value,
| > Cells(Index + 8, 2))
| >
|

You are losing track of the worksheet, so the range and cell functions
are getting lost - an active chart doesn't have cells, etc.

Grabbing the correct worksheet before making the chart will fix the
problem:

Dim WS As Worksheet

Index = 150

Set WS = Worksheets("Sheet1")

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = _
WS.Range(WS.Cells(8, 1), WS.Cells(Index + 8, 1))
ActiveChart.SeriesCollection(1).Values = _
WS.Range(WS.Cells(8, 2), WS.Cells(Index + 8, 2))

[Kevin Long]

Worked perfectly. Thank you.

Kevin

"Steve Gerrard" <my********@comcast.net> wrote in message
news:_I********************@comcast.com...

"Kevin Long" <su*********@hotmail.com> wrote in message
news:mV*********************@news20.bellglobal.com ...
|
| ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1),
Cells(Index +
| 8, 1))
| ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2),
Cells(Index + 8,
| 2))
|
| "Kevin Long" <su*********@hotmail.com> wrote in message
| news:Aj*********************@news20.bellglobal.com ...
| > Hi everybody. I was wondering if somebody out there could tell me
what is
| > wrong with this code? I'm trying to plot a chart in excel using VB.
For
| > this example, the value of the Index variable is 150, but it could
be
| > anything because it is calculated earlier in the code. I can't see
| > anything wrong with the code myself, but it seems not to like it.
Any
| > help would be appreciated.
| >
| > Index = 150
| >
| > Charts.Add
| > ActiveChart.ChartType = xlXYScatterSmooth
| > ActiveChart.SeriesCollection.NewSeries
| >
| > ActiveChart.SeriesCollection(1).XValues = Range(Cells(8, 1),
| > Cells(Index + 8, 1))
| > ActiveChart.SeriesCollection(1).Values = Range(Cells(8, 2).Value,
| > Cells(Index + 8, 2))
| >
|

You are losing track of the worksheet, so the range and cell functions
are getting lost - an active chart doesn't have cells, etc.

Grabbing the correct worksheet before making the chart will fix the
problem:

Dim WS As Worksheet

Index = 150

Set WS = Worksheets("Sheet1")

Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = _
WS.Range(WS.Cells(8, 1), WS.Cells(Index + 8, 1))
ActiveChart.SeriesCollection(1).Values = _
WS.Range(WS.Cells(8, 2), WS.Cells(Index + 8, 2))