Thank you Jon,

i change a bit of your provided code & get it to work properly.

Thank you everyone who replied.


On May 30, 8:04 pm, "Jon Peltier"
wrote:
1. Left out a "." Change

With ws
iRow = .Range("A" & Rows.Count).End(xlUp).Row

to

With ws
iRow = .Range("A" & .Rows.Count).End(xlUp).Row

2. Set Source Data assumes the range includes X values, Y values, and series
names of all series. You might be better off applying these directly to the
series. Fromhttp://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html:

Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("G3")
.Values = ActiveSheet.Range("G4:G14")
.XValues = ActiveSheet.Range("A4:A14")
End With
End Sub
or in your caseSub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("C1") ' guessing
.Values = ActiveSheet.Range("result")
.XValues = ActiveSheet.Range("names")
End With
End Sub
or
Sub AddNewSeries()
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("C1") ' guessing
.Values = rng1
.XValues = rng
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"kiwis" wrote in message

ups.com...



Hi

I have some trouble with my code such that the source range will be
dynamic
Hopping that someone can enlighten my code, i have tried to debug but
can't get it to work.

Sub tapeagechart()

Dim ws As Worksheet
Dim rng As Range, rng1 As Range
Dim iRow As Long

Set ws = Worksheets("Sheet2")

'find last row
With ws
iRow = .Range("A" & Rows.Count).End(xlUp).Row
Set rng = .Range("A18:A" & iRow)
rng.Name = "names"
End With

Set rng1 = ws.Range("C18:C" & iRow)
rng1.Name = "result"

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(?????) _
PlotBy:=xlColumns

i am having trouble in this line, i not sure what should i put
in ?????,
i tried putting the range name Range("names" ,"result") but it failed
this range will change according to my data

or i should use serial instead of setsourcedata source?

ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Chart of Tape Age Summary"
.Axes(xlCategory).TickLabels.Orientation = xlUpward
With .Parent
.Top = Range("F18").Top
.Left = Range("F18").Left
.Name = "tapeage"
End With
End With

End Sub

thank you- Hide quoted text -

- Show quoted text -