how to make chart on selected range?

[DongningLi]

I have the following code, almost got what I want, but each time I run
it, it says: run time error 13, type mismatch.

do you know what went wrong? thanksssssss........



Sub AddChart()
Dim chtChart As Chart
Dim Myrange As Range

ActiveSheet.ChartObjects.Delete
'Create a new chart.
Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="Sheet2")
Set Myrange = Selection

With chtChart
.ChartType = xlColumnClustered
'Set data source range.
.SetSourceData Source:=Sheets("Sheet2").Myrange, PlotBy:= _
xlRows
.HasTitle = True
.ChartTitle.Text = "=Sheet2!R1C1"
'The Parent property is used to set properties of
'the Chart.
With .Parent
.Top = Range("F9").Top
.Left = Range("F9").Left
.Name = "ToolsChart2"
End With
End With
End Sub

 

[Chad]

Hi

If you change your code slightly it will work OK. The range you
selected becomes unselected when you delete the chart, so you need to
select it again. This can be done by declaring your range up front
and selecting it later.

Take care

Chad


Sub AddChart()
Dim chtChart As Chart
Dim Myrange As Range

Set Myrange = Selection
ActiveSheet.ChartObjects.Delete
'Create a new chart.
Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject,
Name:="Sheet2")
Myrange.Select

With chtChart
.ChartType = xlColumnClustered
'Set data source range.
'.SetSourceData Source:=Sheets("Sheet2").Myrange,
PlotBy:=xlColumns
.SetSourceData Source:=Myrange, PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "=Sheet2!R1C1"
'The Parent property is used to set properties of
'the Chart.
With .Parent
.Top = Range("F9").Top
.Left = Range("F9").Left
.Name = "ToolsChart2"
End With
End With
End Sub