Link to home
Start Free TrialLog in
Avatar of Maliki Hassani
Maliki HassaniFlag for United States of America

asked on

Excel: VBA add trendline


I have been looking in your files and can't seem to get a clear answer in reference to how to add a trendline to my charts.  I even tried recording the macro and still just get error message.

Any thoughts?
Sub UpdateGenericChartArray(ByVal lbItemCount As Integer, dataRange As String, dataSheet As String, chartSheet As String, chartID As String, obUsed As Boolean)
Dim myRng As Variant
Dim Target As String
Dim i As Integer
Dim mySeries As Series
Dim myRange As Range
Dim foundData As Boolean

    foundData = False
    With ActiveChart
        i = .SeriesCollection.Count - 1
        For Each mySeries In .SeriesCollection
            If i > 0 Then
                i = i - 1
            End If
        Next mySeries
    End With
    For i = 0 To lbItemCount - 1 'for each item in the listbox that was selected
        Target = lbArray(i) 'keep the code as similar as possible to original combobox routine
        On Error Resume Next
        If Not obUsed Then obMainChart = 2 'go with last 8 weeks as default, to keep all your charts working and to chart things that don't have the option button for date range, they will go with last 3 months
        myRng = Application.WorksheetFunction.VLookup(Target, Range(dataRange), Range(dataRange).Columns.Count - obMainChart, False)
        On Error GoTo 0

        If Err.Number = 0 And Not IsEmpty(myRng) Then
            With ActiveChart
                If i = 0 Then 'set x-axis labels only once
                    Set myRange = Range(myRng)
                    .SeriesCollection(1).XValues = "'" & dataSheet & "'!" & Range(Cells(5, myRange.Cells(1, 1).Column), Cells(5, myRange.Cells(1, myRange.Columns.Count).Column)).Address
                End If
                If i > 0 Then .SeriesCollection.NewSeries ' don't create the new series till past the first one
                .SeriesCollection(i + 1).Values = "'" & dataSheet & "'!" & Range(myRng).Address
                .SeriesCollection(i + 1).Name = Left(Target, InStr(Target & " ", " ") - 1) 'add a space after, just in case it doesn't have a first one -> name = initial word before a blank space of the combobox selection
            End With
        foundData = True
            'do nothing
            foundData = False
        End If
    Next i
    'now format the resulting chart - do this outside the loop as the last one is the only one displayed, anyway - more efficient...
    If foundData Then
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = Target
            Selection.TickLabels.NumberFormat = Range("'" & dataSheet & "'!" & myRange.Cells(1, 1).Address).NumberFormat
        End With

        With ActiveChart
            .HasTitle = True
            .ChartTitle.Text = "CAN'T FIND : " & Target
        End With
    End If
End Sub

Open in new window

Avatar of SiddharthRout
Flag of India image

I haven't checked the code but to add a trend line you can use this simple code?


Avatar of Maliki Hassani


I keep getting an error message : Runtime error: Method'Add'of object'Trendlines'failed

I think it is because there is already a With ActiveChart in the code.

If foundData Then
        With ActiveChart

Avatar of Dave
Can you pls post your file


Thank you for responding..  Here you go!

FYI: All of the large charts in the worksheet are fired by the code that I posted. NOC-Reports-r20.xlsm
That chart type doesn't appear to support Trendlines - the option is greyed out manually

If the chart type is chnaged to x-y then a Trendline can be added
Would you be able to provide me with the code so when I change the chart I will be able to add the trendline??
Okay so I changed the chart and added

This will add the trendlines for both ID's that have been selected to be graphed, but if there is only 1 choice to be charted it will error.  Just as if I picked 3, 4 etc.

I need a way to write an if then statement or something.  Do you have any ideas?
Avatar of Dave
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Pefect!  I can also creat a butto outside of the chart that can turn on trendlines..  Works!  Thanks