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

should just need "Result" as that is the named range, although rng1
without quotes would probably work just as well!
On 30 May, 08:59, kiwis wrote:
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

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/ChartsH...ChartVBA.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

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 -