Horizontal Average Line on Vertical bar Chart

[Sherifa]

I want to adapt my code to add a horizontal average line to my bar chart.

Sub AddDeals()
'
' AddDeals Macro


Dim Wkb As Excel.Workbook
Dim ws As Sheets
Dim k&, s&
 
Set Wkb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets
ws_count = Wkb.Worksheets.Count
                
                   
        'Begin loop
        For i = 4 To ws_count
    
                      
        Wkb.Sheets(i).Activate
        ActiveSheet.Range("Q3:r12").Select
        ws(i).Shapes.AddChart(xlColumnClustered, _
            Left:=ws(i).Range("Q19").Left, _
            Top:=ws(i).Range("Q19").Top, _
            Width:=ws(i).Range("Q19:X19").Width, _
            Height:=ws(i).Range("Q19:Q44").Height).Select
            
         
   With ActiveChart
    'Title
    .HasTitle = True
    .ChartTitle.Characters.Text = Range("A1")
    
    'Primary Axis
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "No of Deals"
    
    .SeriesCollection(1).Trendlines.Add Type:=xlMovingAvg, Name:="No of Deals"
        With .SeriesCollection(1).Trendlines(1).Format.Line
        .Weight = 1
        .ForeColor.SchemeColor = 10
        End With
        
    End With

    'Average Line
    k = ws(i).[r13]
    With ActiveChart
        s = .SeriesCollection.Count
        With .SeriesCollection.NewSeries
            .ChartType = xlXYScatterLines
            .MarkerStyle = xlNone
            'Add a Horizontal Line
            .Formula = Replace("=SERIES(,{.;.},{1;0}," & s & ")", ".", k)
            .Name = "Average"
        End With

        
    End With
    
    'Axis Title
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    
   Next
End Sub

 

[Sherifa]

My solution, I added an average column in my data.
My tip is if you are stuck on charts, try and visualise what you would do on Excel and it will help when debugging the code.

Sub AddDeals()'
' AddDeals Macro


Dim Wkb As Excel.Workbook
Dim ws As Sheets


Set Wkb = ThisWorkbook
Set ws = ThisWorkbook.Worksheets
ws_count = Wkb.Worksheets.Count
                
                   
        'Begin loop
        For i = 6 To ws_count
    
                      
        Wkb.Sheets(i).Activate
        ActiveSheet.Range("Q3:[B]s12[/B]").Select
        ws(i).Shapes.AddChart(xlColumnClustered, _
            Left:=ws(i).Range("Q19").Left, _
            Top:=ws(i).Range("Q19").Top, _
            Width:=ws(i).Range("Q19:X19").Width, _
            Height:=ws(i).Range("Q19:Q44").Height).Select
            
         
   With ActiveChart
    'Title
    .HasTitle = True
    .ChartTitle.Characters.Text = Range("A1")
    
    'Primary Axis
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "No of Deals"
    
    .SeriesCollection(1).Trendlines.Add Type:=xlMovingAvg
    
        With .SeriesCollection(1).Trendlines(1)
            .Border.ColorIndex = 33
        End With
        
        With .SeriesCollection(1).Trendlines(1).Format.Line
        .Weight = 1


        End With
        
    End With
    
    'Average Horizontal Line Line
   
[B]    With ActiveChart[/B]
[B]        With .SeriesCollection(2)[/B]
[B]            .ChartType = xlLine[/B]
[B]            .AxisGroup = 1[/B]
[B][B]            .Name = "A[/B]verage"[/B]
[B]            .XValues = Range("s4:s12")[/B]
                              


        End With
        
     End With
    
    'Axis Title
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    
   Next
End Sub