Simple VBA Question
Bernie - Hey, thanks for trying to help. I guess its not that simple.
I got "An Unable to Set the Values of the Series Class" error message.
If you have a chance, would you mind looking at the code below and see
if you can't figure out how I might tweak it to plot 2 series? The
code works great for 1 series. I would greatly appreciate your help.
Mike
Dim lngRow As Long
Dim lngStartRow As Long
Dim chtDeer As Chart
Dim shtData As Worksheet
Dim rngXData As Range
Dim rngYData As Range
Dim strCounty As String
'
Set shtData = Worksheets("Sheet1")
lngRow = 2
lngStartRow = 2
Do While shtData.Cells(lngRow, 1) < ""
If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1)
Then
Set rngXData = shtData.Range( _
"B" & lngStartRow & ":B" & lngRow)
'Set rngYData = rngXData.Offset(0, 1)
Set rngYData = rngXData.Offset(0, 1).Resize(, 3)
strCounty = shtData.Cells(lngRow, 1).Value
' make a chart
Set chtDeer = Charts.Add
With chtDeer
Do While .SeriesCollection.Count 0
.SeriesCollection(1).Delete
Loop
.ChartType = xlXYScatterLines
.PlotBy = xlColumns
With .SeriesCollection.NewSeries
.XValues = rngXData
.Values = rngYData
End With
.Location Whe=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = strCounty
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.HasLegend = False
.Name = strCounty
End With
lngStartRow = lngRow + 1
End If
lngRow = lngRow + 1
Loop
Set rngXData = Nothing
Set rngYData = Nothing
Set shtData = Nothing
Set chtDeer = Nothing
End Sub
Bernie Deitrick wrote:
Try
Set rngYData = rngXData.Offset(0,1).Resize(,3)
HTH,
Bernie
MS Excel MVP
wrote in message ups.com...
Currently using the following code to grab an adjacent column:
Set rngYData = rngXData.Offset(0,1)
later on in the program:
.XValues=rngXdata
.Values=rngYdata
The above allows me to plot the following data:
County Year Estimate1
a 1981 500
a 1982 650
I would like to be able to add another series (or 2) and plot it.
County Year Estimate1 Estimate2 Estimate3
a 1981 500 590 600
a 1982 650 750 800
Can I adapt the above code to allow me to do this?
Mike
|