Solved

Excel: VBA add trendline

Posted on 2011-02-16
9
831 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
  • 5
  • 3
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
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
Comment Utility
Can you pls post your file

Cheers

Dave
0
 

Author Comment

by:Maliki Hassani
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Pefect!  I can also creat a butto outside of the chart that can turn on trendlines..  Works!  Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now