Including a count result into a subtraction formula

Here is my challenge:

Range E20:P20 in an Excel Spreadshet calculates the difference between Sales Forecast values shown in Range E15:P15 and Sales business plan values shown in Range E5:P5 . The formula applied in range E20:P20 is as follows:

ActiveCell.FormulaR1C1 = "=R[-5]C-R[-15]C

Since I have to add new lines into the spreadsheet each month with new forecast data, the number of lines between forecast and business plan is increasing.

My idea to create a flexible deviation formula is to use a count operation by running a loop to establish the number of lines between the business plan line and the forecast line that will move down each month.

I have been doing fine counting the difference using marks in column A (P for the business plan line and x for the deviation line) but now I do not know how to correctly include the result of the counting into the subtraction formula (see last line before "End Sub" line for my current attempt).

Perhaps it will be easier if I can send you the file. Please provide me an email address for this if possible.


The Macro code:

Sub DeviationFormulaAdjustment()

ActiveCell.Select
Dim Count As Long
Count = 0
Do
Count = Count - 1
ActiveCell.Offset(-1, 0).Select
Loop Until ActiveCell = "P"

Do Until ActiveCell = "x"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = "=R[-5]C-R[Count]C"

End Sub