Automatic graph generation macro

[hhh79bigo]

Hi,

I am trying to write a macro that generates a graph/chart from specific data points in a spreadsheet, there should be two points in each series. Below is what I have written so far, however I get a runtime error "438" Object doesn't support this property or method. Where am I going wrong?

Thanks

O

Sub Mesh()
Dim n As Integer, i As Integer
n = Range("Q127")
'
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    For i = 1 To n
       ActiveChart.SeriesCollection.NewSeries
       ActiveChart.SeriesCollection(i).XValues = ActiveSheet.Range("R131", "T131").Offset(i)
       ActiveChart.SeriesCollection(i).Values = ActiveSheet.Range("S131", "U132").Offset(i)
       ActiveChart.Location Where:=xlLocationAsObject, Name:="Mesh"
       With ActiveChart
           .HasTitle = False
           .Axes(xlCategory, xlPrimary).HasTitle = False
           .Axes(xlValue, xlPrimary).HasTitle = False
       End With
    Next
End Sub

[Bob Phillips]

Activesheet gets changed to the chart sheet.

Sub Mesh()
    Dim n As Integer, i As Integer
    
    n = Range("Q127")
     '
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Mesh"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    For i = 1 To n
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(i).XValues = ActiveSheet.Range("R131", "T131").Offset(i)
        ActiveChart.SeriesCollection(i).Values = ActiveSheet.Range("S131", "U131").Offset(i)
    Next
End Sub

[hhh79bigo]

Thanks for your response! It is appreciated, however when I come to run this modification, a new error occurs, Runtime error '1004' Method 'HasTitle' of object '_Chart' failed

Thanks for any help with this

Regards

Owen

[Bob Phillips]

I didn't get that, but looking at the code it may be because the chart is moved before allocating the name. See if this works better

Sub Mesh()
    Dim n As Integer, i As Integer
     
    n = Range("Q127")
     '
    Charts.Add
    With ActiveChart
    
        .ChartType = xlXYScatterLinesNoMarkers
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
        .Location Where:=xlLocationAsObject, Name:="Mesh"
    End With
    For i = 1 To n
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(i).XValues = ActiveSheet.Range("R131", "T131").Offset(i)
        ActiveChart.SeriesCollection(i).Values = ActiveSheet.Range("S131", "U131").Offset(i)
    Next
End Sub

[snb]

Sub M_snb()
    sn = Sheet1.Range("R131").CurrentRegion
    
    With Sheet1.ChartObjects.Add(Cells(10, 3).Left, Cells(10, 3).Top, 200, 80)
       With .Chart
        .ChartType = 75
        .HasTitle = False
        .Axes(1, 1).HasTitle = False
        .Axes(2, 1).HasTitle = False

        For j = 1 To UBound(sn)
            With .SeriesCollection.NewSeries
                .XValues = Array(sn(j, 1), sn(j, 3))
                .Values = Array(sn(j, 2), sn(j, 4))
            End With
        Next
       End With
    End With
End Sub

[hhh79bigo]

Thanks for your help it's now working!