Horizontal line in chart

[G2K]

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

 

[Richard Schollar]

I *think* you'll need to place that line on a secondary axis to the chart (and you'll have to calculate what value it should be ascribed to). The basic principle is outlined here:

http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine

 

[G2K]

Thanks rechard,

I read this page before posting my question here. and i know about the secondary axes, but i do not want to add extra column in datasheet or write value in cells to draw the horizontal line as i have 42 charts in my worksheet. Can we keep Average value in variable and draw a line on this value???

thanks again for your reply

 

[Richard Schollar]

Being no expert on the use of charts I'm afraid I am not sure - perhaps you could just have a hidden sheet with all these secondary axis values that the charts would refer to? Then you needn't see the average data.

 

[G2K]

thanks Rechard,

i will try....... thanks for your time

 

[pgc01]

Hi

Can you explain?

...
 
Set myChtObj = ActiveSheet.ChartObjects.Add _
    (Left:=50, Width:=375, Top:=75, Height:=225)
[COLOR=black][/COLOR]
[COLOR=black]myChtObj.Chart.SetSourceData Source:=Sheets("Graph").Range("C1:P" & rCount)
[/COLOR]
myChtObj.Chart.ChartType = xlColumnClustered

With myChtObj.Chart
    Do Until .SeriesCollection.Count = 0
        .SeriesCollection(1).Delete
    Loop
End With

...

Why do you add the all those series to the chart just to delete them immediately after?

 

[G2K]

myChtObj.Chart.SetSourceData Source:=Sheets("Graph").Range("C1:P" & rCount)

- above line creates chart of selected range say - C1 to P30, and represents all column data in a single chart which is difficult to analyse.So i delete all the series first and then create a new series.

Please let me know if there are other methods to get this output.

also,Can we draw a horizontal line without adding one more column in my original data range

Thanks

 

[pgc01]

Hi

I think that if you only need 1 series that's the only one you should add.

To draw the horizontal line at Y = average value, you need to calculate that value. Since it's a straight line you only need 2 points to draw it.

Try:

Sub AddChartObject()
Dim myChtObj As ChartObject
Dim lRow As Long
Dim dAvg As Double
 
lRow = Range("C50000").End(xlUp).Row
 
Set myChtObj = ActiveSheet.ChartObjects.Add _
    (Left:=50, Width:=375, Top:=75, Height:=225)
 
With myChtObj.Chart
 
    .ChartType = xlColumnClustered
 
    With .SeriesCollection.NewSeries
        .Name = ActiveSheet.Range("C1")
        .Values = ActiveSheet.Range("P3:P" & lRow)
        .XValues = ActiveSheet.Range("C3:C" & lRow)
    End With
 
    .HasTitle = True
    .ChartTitle.Characters.Text = Range("P1").Value
    .HasLegend = False
    
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Values(M)"
        .MajorGridlines.Delete
    End With
    
    With .Axes(xlCategory, xlPrimary)
        .HasTitle = True
        .AxisTitle.Characters.Text = "Comapany Name"
        .CrossesAt = 1
        .TickLabelSpacing = 1
        .TickMarkSpacing = 1
    End With
    
    ' calculate the average
    dAvg = Application.WorksheetFunction.Average(ActiveSheet.Range("P3:P" & lRow))
    
    ' add a series with a horizontal line with Y equal to the average value
    With .SeriesCollection.NewSeries
        .ChartType = xlXYScatterLinesNoMarkers
        .Name = "Avg"
        .Values = Array(dAvg, dAvg)
        .XValues = Array(0.5, ActiveSheet.Range("P3:P" & lRow).Rows.Count + 0.5)
    End With
    
End With
End Sub

 

[G2K]

Thanks a ton.

it's working like a charm.......... Learned someting today

Many Thanks