This looks at your Y values and returns 0 if #N/A or blank else 1.

The formula I used for in the helper column was: =IF(OR(ISNA(D10),ISBLANK(D10)),0,1) I had already used a helper column before I saw Missing values in MS Excel LINEST, TREND, LOGEST and GROWTH functions If targetTrend.Type = -4133 Then Debug.Print Loop multiple series getting the trend formulas: Dim currentSeries As Longįor currentSeries = 1 To m圜hartSeriesCollection.Countįor Each targetTrend In m圜hart.SeriesCollection(1).TrendLines The enumerations for trend type can be found here, or via the object browser through members of the class XLTrendlineType. ' If Left$(targetTrend.Name,3) = 'Log' Then Msgbox 'Log' If targetTrend.Type = -4133 Then Msgbox 'Log' Ws.Range('A'& nextRow) = Ĭhecking the trend type: For Each targetTrend In m圜hart.SeriesCollection(1).TrendLines LastRow = ws.Cells(ws.Rows.Count,'A').End(xlUP).Row Set targetTrend = targetChart.SeriesCollection(1).Trendlines(1) ' change as appropriate Set targetChart = ws.ChartObjects('Chart 1').Chart ' change as required

Set ws = wb.Worksheets('Sheet1') ' change as appropriate If you really want to be sure you are getting the right equation, or to get multiple you could iterate over charts, chartseries and trendlines.Īfter the main code I include an example of checking that the target series trend type is Logarithmic.Ĭode version which writes out the equation to the next available row in column A of worksheet 1. Then pushing the button will execute the macro. Then associate that command button with the macro GetTrendlineEquation (right click button and associate should do it). Generally, less problematic than ActiveX objects. Ribbon > developer tab > controls > form controls. a command button from the forms control is simpliest. You could tie this to a Worksheet_Change event where the target is your Y range, but set it so is only triggered once per updateĢ) You could tie to the execution of the macro to a control.Į.g. The advantage below is to show you how to start checking the trend type is the one you want and serves as the basis for expanding to loop over other series if needed. I also give an in sheet method for completeness. DataLabel.Text to write out the formula using VBA.

Excel trendline equation to cell