dynamic chart range in vba

[felix]

hi there,
i have tried several ways of trying to get this dynamice range to work
with my chart. each time i have tried a different error, but this is
the current versio, and the current error is:
Application-defined or object-defined error.
if anyone could shed some light on this for me, it would be muchly
appreciated.
The Code:

' draw the graph of the imported data
Graph:
sName = Sheets(Sheets.Count).Name
Set rng = Range("D6").Offset(s, 1)

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets(sName).Range(rng), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Crush Data"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = sName
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Displacement (mm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Force (kN)"
End With
Range("A1").Select

 

[felix]

the crush data was just a constant.
i have now managed to fix it and get it to work.
the rng was part of the problem.
this is the new code:
' draw the graph of the imported data
Graph:
sName = Sheets(Sheets.Count).Name
Set rng = Sheets(sName).Range("D6").Resize(s, 2)

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Name = "=""Crush Data"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=sName
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = sName
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Displacement (mm)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Force (kN)"
End With
Range("A1").Select

i had a look around your site before, but as they are only for charts
that are to be dynamic once created, whereas i needed this to be
dynamic and created on the fly.
you have a nice site though, plenty of information. clean coding on
the html too, as its not using frames (well, i couldnt pick it up
anyway)

cheers, felix