Excel FORECAST on pre-determined number.

[S.H.A.D.O.]

Good afternoon,

I have a two ranges of data in cells E15:K? And M15:S?
I have a figure in cell A1 that increases by one when new data is added to the bottom of the ranges above.
The code below works and does exactly what I want it to do...

Sub FORECAST_Next_Numbers()
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
   
    Range("E10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(E15,,,$A1,1),OFFSET($B15,,,$A1,1))"
           
    Range("M10").Resize(1, 7).Formula = _
            "=FORECAST(INDIRECT(""B"" & $A1+15),OFFSET(M15,,,$A1,1),OFFSET($B15,,,$A1,1))"
           
    Range("E10:S10").Value = Range("E10:S10").Value
    Range("E10:K10,M10:S10").NumberFormat = "0"
   
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

I want to be able to forecast from the bottom of the data up by the figure in cell B1 as opposed to the whole of the data.
So for example, if I had the number 50 in cell B1, I just want it start at the last figure in column E and calculate the FORECAST up on the last 50 numbers.

I hope this makes sense.

Thanks in advance.

 

[Kenneth Hobson]

I guess Offset() could be used with Column() to make the first one more fill worthy.

FWIW, the only reason to use absolute references "$" in a formula is for manual filling purposes.

Had you done many of those, we could use a loop.

 

[S.H.A.D.O.]

Thanks Kenneth,

I will have a look tomorrow to see if I can come up with a loop to put the results in E10:S10?

 

[Kenneth Hobson]

Sub Space1999()
  Dim s$, s2$, c$, r As Range, i As Integer
  Set r = [E10]
  s2 = "INDIRECT(""B""&A1+15-B1&"":B""&A1+14),A1+1)"
  For i = 5 To 19 'columns E to S
    c = Split(r.Address, "$")(1)
    s = "=TREND(INDIRECT(""" & c & """&A1+15-B1&"":" & c & """&A1+14),"
    r.Formula = s & s2
    Set r = r.Offset(, 1)
  Next i
End Sub

 

[S.H.A.D.O.]

Brilliant, thank you so much for your time and help Kenneth .

Just one final question please, how can I get it to skip column L as there is no data in this column at all.

Thanks again.

 

[Kenneth Hobson]

While you could do 2 loops or use an IF, it is just as easy to cleanup after the loop. e.g.

[L10].ClearContents

 

[S.H.A.D.O.]

Brilliant, thanks Kenneth.

I like the Sub title of the code you posted by the way, very clever.
I used to watch UFO (S.H.A.D.O.), Space 1999 and Blake's 7 as a kid and they are now classed as cult series.
Unfortunately they don't make things like those these days.

Thanks again for all your help and time, it is very much appreciated.