Solved

Excel: VBA add trendline

Posted on 2011-02-16
9
867 Views
Last Modified: 2012-05-11
Experts,

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
    
    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

Open in new window

0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34912413
I haven't checked the code but to add a trend line you can use this simple code?

ActiveChart.SeriesCollection(1).Trendlines.Add

Sid
0
 

Author Comment

by:Maliki Hassani
ID: 34912467
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

thoughts?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34912748
Can you pls post your file

Cheers

Dave
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Maliki Hassani
ID: 34912768
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34912976
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
0
 

Author Comment

by:Maliki Hassani
ID: 34913114
Would you be able to provide me with the code so when I change the chart I will be able to add the trendline??
0
 

Author Comment

by:Maliki Hassani
ID: 34913182
Okay so I changed the chart and added
.SeriesCollection(1).Trendlines.Add
.SeriesCollection(2).Trendlines.Add

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?
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 34913244
something like this

Cheers

Dave
Sub Trends()
    Dim chrSer As Series
    With ActiveChart
        For Each chser In .SeriesCollection
            chser.Trendlines.Add
        Next
    End With
End Sub

Open in new window

0
 

Author Closing Comment

by:Maliki Hassani
ID: 34913260
Pefect!  I can also creat a butto outside of the chart that can turn on trendlines..  Works!  Thanks
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question