Add 3 sets of data to existing graph and then step down to next row and repeat

[flynch]

Per row, I have the name, the Y value and three sets of X values. For example:

Name, Y value, X value 1, X value 2, X value 3
Name, Y value, X value 1, X value 2, X value 3
Name, Y value, X value 1, X value 2, X value 3
(and so on..)

I am trying to plot each row onto an existing scatter graph (where each name has three sets of points) and then for it to step down to the next row and repeat the same process.

I have the code:

Sub summaryGraph()
Worksheets("PE summary").ChartObjects(1).Activate
Do While Index < 200
        Call MySummaryGraph(i)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Function MySummaryGraph(I)

With ActiveChart.SeriesCollection.NewSeriesSetsourceData

        .Name = ActiveSheet.Range("AG3")
        .Values = ActiveSheet.Range("AF3")
        .XValues = ActiveSheet.Range("AJ3")
End With
With ActiveChart.SeriesCollection.NewSeries
        .Name = ActiveSheet.Range("AG3")
        .Values = ActiveSheet.Range("AF3")
        .XValues = ActiveSheet.Range("AK3")
End With
With ActiveChart.SeriesCollection.NewSeries

        .Name = ActiveSheet.Range("AG3")
        .Values = ActiveSheet.Range("AF3")
        .XValues = ActiveSheet.Range("AL3")
        End With
End Function

But it is coming up with the error message "Error 91: Object variable or With block variable not set"

I'm really new to vba so if this is completely wrong I apologise!

 

[RoryA]

Welcome to the forum.

This line is incorrect:

With ActiveChart.SeriesCollection.NewSeriesSetsourceData

It should just be:

With ActiveChart.SeriesCollection.NewSeries

 

[flynch]

Welcome to the forum.

This line is incorrect:

With ActiveChart.SeriesCollection.NewSeriesSetsourceData

It should just be:

With ActiveChart.SeriesCollection.NewSeries

Apologies, when I copied and pasted, I pasted the code I was messing with. My actual code reads:

With ActiveChart.SeriesCollection.NewSeries

 

[flynch]

Apologies, the line "With ActiveChart.SeriesCollection.NewSeriesSetsourceData" is actually:

With ActiveChart.SeriesCollection.NewSeries

I pasted the wrong part of the code

 

[RoryA]

On which line does the error occur?

 

[flynch]

On which line does the error occur?

It appears on the line

With ActiveChart.SeriesCollection.NewSeries

 

[RoryA]

Looking at your main routine again, you are selecting a cell after updating the first chart, which means there won't be a chart active for the second pass.

Can you explain what you're trying to do as your code makes no sense to me. Your loop control variable, Index, is never set or incremented anywhere, and you pass another undeclared variable, i, to your routine which never uses it. So in the end you seem to have a never ending loop that simply creates more and more of the same 3 series on the same chart.

 

[flynch]

Looking at your main routine again, you are selecting a cell after updating the first chart, which means there won't be a chart active for the second pass.

Can you explain what you're trying to do as your code makes no sense to me. Your loop control variable, Index, is never set or incremented anywhere, and you pass another undeclared variable, i, to your routine which never uses it. So in the end you seem to have a never ending loop that simply creates more and more of the same 3 series on the same chart.

I have three sets of data per project. So for every project (per row), I have a y value and three x values to be plotted on a graph. I have 64 projects. Therefore, I want the graph to plot all 3 data sets for a project and then go down to the next row and repeat the same thing. In the end I should have 192 dots on a scatter graph if that makes sense.

 

[RoryA]

OK then, my interpretation of that would be something like this:

Sub summaryGraph()
Dim i as long
Dim ch as Chart
Set ch = Worksheets("PE summary").ChartObjects(1).Chart
For i = 1 to 64
        Call MySummaryGraph(i + 2, ch)
Next i
End Sub
Sub MySummaryGraph(I as Long, cht as Chart)

With Cht
   with .SeriesCollection.NewSeries
        .Name = ActiveSheet.cells(i, "AG")
        .Values = ActiveSheet.Cells(i, "AF")
        .XValues = ActiveSheet.Cells(i, "AJ")
   End With
With .SeriesCollection.NewSeries
        .Name = ActiveSheet.Cells(i, "AG")
        .Values = ActiveSheet.Cells(i, "AF")
        .XValues = ActiveSheet.Cells(i, "AK")
End With
With .SeriesCollection.NewSeries

        .Name = ActiveSheet.Cells(i, "AG")
        .Values = ActiveSheet.Cells(i, "AF")
        .XValues = ActiveSheet.Cells(i, "AL")
        End With
End With
End Sub

 

[flynch]

Amazing thank you! Works brilliantly.... I need to get better at this!

Thanks again