Change the forward on a trendline using VBA
I hope someone can help.
I have an Excel workbook with a number of line charts, each of which has a trend line. The data for each chart is added to each week up to 18 weeks and the chart changes using named range and offset. The problem is that the trendline for each chart is set at forward to a fixed value, what I would like to do is have the trendline stay at forward to the 18th week regardless of number of data entries that are being plotted eg if I have 14 data entries the forward would be 4, if I had 15 the forward would be 3 etc. Just so I don't have to alter the forward value each week for each chart.
Can this be done? Any help would be appreciated, I am a bit of a novice at VBA.
Many thanks in advance for any help.