VBA Variable in Sum formula

How about this. If I was trying to put a formula in F9 = If(B9="E", SUM(D6:E8), "")
Now, the amount of rows in between an "E" occurring in Column B changes and that is why I have a count going on in Column B. For Sum(Cell1, Cell2); Cell1 needs to equal D6(notice that in B8 the count is 3).
I need VBA to input a formula =If(B9="E", SUM(ActiveCell.Offset(-B8,-2):ActiveCell.Offset(-1,-1)), "") in Cells (F2:F5000)
I am getting an error when I try just putting that formula directly above into a cell in Excel. I realized that I cannot put VBA programming on the sheet itself.
Is there a way to get the end desired function above thru vb?

___A_B_C_D_E_F
5____E___2_3
6____1___4_5
7____2___6_7
8____3___8_9
9____E______Sum()

Another way to look at it would be I need to take a sum of the cells that are (-x rows, -2 columns) : (-1 rows, 0 columns) while the variable x is a number stored in another cell, which we could call B8.
How could I accomplish this through VBA?