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!