Range select macro - Trendline (Moving Average)

[Wayne01]

Hi all

I have a macro that applies a trendline (moving average type) to my data series. The moving average period is specified by the user from a popup InputBox (see code below).

Sub Add_Red_Trendlines()
Dim TrendNum
TrendNum = InputBox(Prompt:="Insert Moving Average Period Below:", _
Title:="MOVING AVERAGE PERIOD", Default:="INSERT NUMBERS ONLY")
If TrendNum = "INSERT NUMBERS ONLY" Or _
TrendNum = "" Or _
TrendNum = vbaCancel Then
Exit Sub
End If
If Not IsNumeric(TrendNum) Then
MsgBox "ONLY VALID NUMBERS ALLOWED!", vbCritical, Title:="WARNING TO USER"
Exit Sub
End If
If IsNumeric(TrendNum) Then
    ActiveSheet.ChartObjects(1).Activate
    If ActiveChart.SeriesCollection.Count > 0 Then
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlMovingAvg, Period:=TrendNum _
        , Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False).Select
        With Selection.Border
        .ColorIndex = 3
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
'................ additional code removed.........
End Sub

Unfortunately, my data is arranged in a column with the most recent data positioned at the uppermost row so the moving average trendline prematurely terminates before the end of the chart by the number of periods the moving average is (eg. if I apply a 30 period moving average, the trendline terminates 30 periods before the last data period plotted on the chart).

By default, it appears that an excel trendline (moving average type) applied to a range in a column is calculated from top to bottom starting at the uppermost row rather than from bottom to top as I would like. So the easy solution was to reverse the order of my data within the column. However, this had an adverse impact on the functionality of other features of my spreadsheet so I have to find another solution.

Question: Can you suggest a solution to force excel to calculate a trendline (moving average type) in reverse order? If you could, that would be great and it would save a lot of work.

An alternative is to insert a formula (eg. "AVERAGE(range of cells to be selected here)") into cells within a vacant column and plot the data as a new series on the chart. If I adopt this approach, I would like to retain the ability for the user to select the moving average period by entering the specified period into an InputBox popup as per my existing code.

Question: Can you suggest a vba solution that would accomplish this task?

As always, thanks for your help.

Ps. Still using Excel 97.

Kind regards

Wayne

 

[Wayne01]

bump

 

[Wayne01]

Any suggestions?

Thanks again.

Kind regards

Wayne