Trouble creating a chart

[jdh]

I am trying to create a chart with multiple series in the active worksheet. I
am trying to plot up column A vs B for each series (The Series Name is in
column C). Each series is of varying length, so I loop through the list name
(column C) and compare it to a short set of Series Names in Column F. I am
getting errors with the Set NewSrs statement. Do I have the wrong variable
types defined? Any help would be appreciated.

Dim i, j, k, l As Long
Dim sName, cName, seName As String
Dim oChart As Chart
Dim NewSrs As Series

sName = ActiveSheet.Name

Set oChart = Charts.Add
oChart.ChartType = xlXYScatter
oChart.Location Where:=xlLocationAsObject, Name:=sName

k = 0
For i = 1 To Range(Sheets(sName).Range("C1"),
Sheets(sName).Range("C1").End(xlDown)).Count
If Sheets(sName).Range("C1").Offset(i, 0) =
Sheets(sName).Range("F1").Offset(k, 0) Then
j = j + 1
Else
Set NewSrs = oChart.SeriesCollection.NewSeries
With NewSrs
' Name of the first set of data
.Name = Sheets(sName).Range("F1").Offset(k, 0).Value
' X-Values in the 1st column
.XValues = Sheets(sName).Range(Cells(l + 1, 2), Cells(j , 2))
' Y-Values in the 2nd column
.Values = Sheets(sName).Range(Cells(l + 1, 3), Cells(j , 3))
End With
j = j + 1
k = k + 1
End If
Next i

 

[Jon Peltier]

oChart is the chart sheet, which you destroyed when you converted the chart
to an embedded chart. Use this syntax instead:

Set oChart = Sheets(sName).ChartObjects.Add(100, 100, 350, 275).Chart

where the numbers in parens are the Left, Top, Width, and Height dimensions
of the chart object. You can use whatever values you want, and even set them
according to a range you wish to cover:

With Sheets(sName).Range("D4:M24")
Set oChart = Sheets(sName).ChartObjects.Add(.Left, .Top, .Width,
..Height).Chart
End With

You should also note that when you Dim a list of variables on one line, each
requires the variable type, like this:

Dim i As Long, j As Long, k As Long, l As Long

In this statement, i, j, and k are all declared as variants, since you
didn't specify a variable type for each:

Dim i, j, k, l As Long

- Jon

 

[jdh]

Thanks for the help. Everything is working great now.