Solved

Excel: VBA add trendline

Posted on 2011-02-16
9
857 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Office 365 not Syncing user object 3 15
Embed XL WS and Objects into a PPTX 8 30
copy down array 24 26
count number 10 27
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

738 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