Excel: VBA add trendline

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
    
    Sheets(chartSheet).ChartObjects(chartID).Activate
    With ActiveChart
        i = .SeriesCollection.Count - 1
        For Each mySeries In .SeriesCollection
            If i > 0 Then
                mySeries.Delete
                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
        Err.Clear
        
        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
            Sheets(chartSheet).ChartObjects(chartID).Activate
            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
        Else
            '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
            .Axes(xlValue).Select
            Selection.TickLabels.NumberFormat = Range("'" & dataSheet & "'!" & myRange.Cells(1, 1).Address).NumberFormat
        End With
    Else

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

Select allOpen in new window