Hi,
i want to add a horizontal line on excle chart.i have code to draw a chart on specified columns -
Sub AddChartObject()
Dim myChtObj As ChartObject
rCount = Range("C50000").End(xlUp).Row
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=50, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.SetSourceData Source:=Sheets("Graph").Range("C1:P" & rCount)
myChtObj.Chart.ChartType = xlColumnClustered
With myChtObj.Chart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
With myChtObj.Chart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("C1")
.Values = ActiveSheet.Range("P3:P" & rCount)
.XValues = ActiveSheet.Range("C3:C" & rCount)
End With
With myChtObj.Chart
.HasTitle = True
.ChartTitle.Characters.Text = Range("P1").Value
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Comapany Name"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values(M)"
.HasLegend = False
With .Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
End With
.Axes(xlValue).MajorGridlines.Delete
End With
End sub
now i have to draw a horizontal line on the average value of column P/Range("P3:P" & rCount).
Many Thanks
i want to add a horizontal line on excle chart.i have code to draw a chart on specified columns -
Sub AddChartObject()
Dim myChtObj As ChartObject
rCount = Range("C50000").End(xlUp).Row
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=50, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.SetSourceData Source:=Sheets("Graph").Range("C1:P" & rCount)
myChtObj.Chart.ChartType = xlColumnClustered
With myChtObj.Chart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
With myChtObj.Chart.SeriesCollection.NewSeries
.Name = ActiveSheet.Range("C1")
.Values = ActiveSheet.Range("P3:P" & rCount)
.XValues = ActiveSheet.Range("C3:C" & rCount)
End With
With myChtObj.Chart
.HasTitle = True
.ChartTitle.Characters.Text = Range("P1").Value
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Comapany Name"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Values(M)"
.HasLegend = False
With .Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
End With
.Axes(xlValue).MajorGridlines.Delete
End With
End sub
now i have to draw a horizontal line on the average value of column P/Range("P3:P" & rCount).
Many Thanks