424 code and graph creation in vba

Question

I'm relatively new to VBA - tend to just go with stuff that works even if I don't understand it I'm afraid!

I'm trying to produce individual scatter graphs for each row of data from a worksheet. I'm OK with configuring the graph etc, but getting it to read each row and iterate properly is beyond me!

This is what I've got currently:

Private Sub CommandButton1_Click()
ActiveWorkbook.Charts.Add
Dim i As Integer
For i = 2 To WS.Range("A65536").End(xlUp).Row
With ActiveWorkbook.ActiveChart
    'Data?
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "Progress"
    .SeriesCollection(1).XValues = "=Sheet2!$B$1:$J$1"
    .SeriesCollection(1).Values = "=Sheet2!$B$" & i & ":$J$" & i

    'Titles
    .HasTitle = True
    .ChartTitle.Characters.Text = "valuefromcellN2?"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade"
    .Axes(xlCategory).HasMajorGridlines = True

    'Formatting
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = False
    .HasLegend = False

End With
Next
End Sub

When I run this, I get a runtime error 424 - object required. My questions are:

1. Am I going about this in roughly the right way?
2. What have I missed out to cause this runtime error?
3. How do I get the valuefromcellN2? to actually show the value, not text?

Thanks for any help!

Sam

EDIT:

thanks for the help - I think I've updated it correctly now (it works with all titles and creates new graphs!)

Private Sub CommandButton1_Click()
ActiveWorkbook.Charts.Add
Dim ws As Worksheet
Set ws = tracker
Dim i As Long
For i = 2 To ws.Range("A65536").End(xlUp).Row
Charts.Add
With ActiveWorkbook.ActiveChart
    'Data?
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "Progress"
    .SeriesCollection(1).XValues = "=tracker!$B$1:$J$1"
    .SeriesCollection(1).Values = "=tracker!$B$" & i & ":$J$" & i

    'Titles
    .HasTitle = True
    .ChartTitle.Characters.Text = Range("N" & i).Value
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade"
    .Axes(xlCategory).HasMajorGridlines = True

    'Formatting
    .Axes(xlCategory).HasMinorGridlines = False
    .Axes(xlValue).HasMajorGridlines = True
    .Axes(xlValue).HasMinorGridlines = False
    .HasLegend = False

End With
Next
End Sub

Answer 1

Q1 Am I going about this in roughly the right way?

Yes you are. You need to just iron out few issues.

Q2 What have I missed out to cause this runtime error?

You are getting that error because you have not declared the ws object. I have explained it later in the code below on how to go about it.

Q3 How do I get the valuefromcellN2? to actually show the value, not text?

To get the value from a particular cell, you can say Range("A1").Value so in your case it will be Range("N2").Value

Few other things

1. When working with Excel rows, avoid the use of Integer. Use Long. Post Excel 2007, the number of rows have increased and the Integer may not be able to accommodate bigger values.

2. Avoid using hard coded values like 65536. See This on how to find the last row.

Code

I have commented the code so you shouldn't have a problem understanding it. But if you do then simply ask.

Is this what you are trying (Untested)?

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim i As Long, LRow As Long
    Dim chrt As Chart  

    '~~> This is where we will have the chart
    '~~> We declare the object here. Change as applicable
    Set ws = ThisWorkbook.Sheets("Sheet2")

    With ws
        '~~. Find the last row in Col A
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Add Chart
        Set chrt = .Shapes.AddChart.Chart

        With chrt '<~~ Work with chart and set it's parameters
            .ChartType = xlXYScatter
            .HasTitle = True
            .ChartTitle.Characters.Text = ws.Range("N2").Value '<~~ Set the Value here
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Timeline"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Grade"
            .Axes(xlCategory).HasMajorGridlines = True

            'Formatting
            .Axes(xlCategory).HasMinorGridlines = False
            .Axes(xlValue).HasMajorGridlines = True
            .Axes(xlValue).HasMinorGridlines = False
            .HasLegend = False

            n = 1

            '~~> Add series and set it
            For i = 2 To LRow
                .SeriesCollection.NewSeries
                .SeriesCollection(n).Name = "Progress"
                .SeriesCollection(n).XValues = "=Sheet2!$B$1:$J$1"
                .SeriesCollection(n).Values = "=Sheet2!$B$" & i & ":$J$" & i
                n = n + 1
            Next i
        End With
    End With
End Sub