need help in my code on generating chart with dynamic range

[kiwis]

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 Where:=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

 

[aidan.heritage]

should just need "Result" as that is the named range, although rng1
without quotes would probably work just as well!

 

[Jon Peltier]

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. From http://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

 

[kiwis]

Thank you Jon,

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

Thank you everyone who replied.