Solved

Excel: VBA add trendline

Posted on 2011-02-16
9
843 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
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

792 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