Calculation of an inflation on volume/year

I would like to find out if my current solution to the problem described below is "good enough" or if there is an alternative way of achieving it. All I care about is the length (no. of lines) of the code and its efficiency. I am trying to follow the "DRY" principle and come up with something that when seen by another developer in the future will be considered a "good practice".

I am forced[1] to enter a few formulas into spreadsheet cells via vba. The formula is for a calculation of an inflation (say labour rate) on volume/year. It has some constants and some variants but the tricky bit is a variable inflation rate added each year. In my real life example this is far more complicated but I have shortened it and came up with an SSCCE.
SSCCE

You can view the example as a spreadsheet on Google Docs, or have a quick look through below:

The formulas are:

D6 = -$B$12*D4*(D8+1)
E6 = -$B$12*E4*(D8+1)*(E8+1)
F6 = -$B$12*F4*(D8+1)*(E8+1)*(F8+1)
G6 = -$B$12*G4*(D8+1)*(E8+1)*(F8+1)*(G8+1)
H6 = -$B$12*H4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)
I6 = -$B$12*I4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)
J6 = -$B$12*J4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)
K6 = -$B$12*K4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)
L6 = -$B$12*L4*(D8+1)*(E8+1)*(F8+1)*(G8+1)*(H8+1)*(I8+1)*(J8+1)*(K8+1)*(L8+1)

I tried it but didn't like it. There are 9 really long and ugly lines (remember, in my real life example there are many more variables).

```
Range("D6").Formula = "-$B$12D4(D8+1)"
Range("E6").Formula = "-$B$12E4(D8+1)*(E8+1)"
Range("F6").Formula = "-$B$12F4(D8+1)(E8+1)(F8+1)"
Range("G6").Formula = "-$B$12G4(D8+1)(E8+1)(F8+1)*(G8+1)"
Range("H6").Formula = "-$B$12H4(D8+1)(E8+1)(F8+1)(G8+1)(H8+1)"
Range("I6").Formula = "-$B$12I4(D8+1)(E8+1)(F8+1)(G8+1)(H8+1)*(I8+1)"
Range("J6").Formula = "-$B$12J4(D8+1)(E8+1)(F8+1)(G8+1)(H8+1)(I8+1)(J8+1)"
Range("K6").Formula = "-$B$12K4(D8+1)(E8+1)(F8+1)(G8+1)(H8+1)(I8+1)(J8+1)*(K8+1)"
Range("L

Here is what I would do if I were you:

Sub Main()
    Dim b12_value As Double
    Dim inflation As Double

    'Seed the inflation variable and store the static B12 value
    inflation = 1
    b12_value = Range("$B$12").value

    'Loop through each cell in D6:L6 and calculate its value.
    For Each cell In Range("D6:L6")
        inflation = inflation * (Cells(8, cell.Column).Value + 1)
        cell.Value = -b12_value * Cells(4, cell.Column).Value * inflation
    Next

End Sub

Since you are concerned about number of lines, I chose not to store the volume for each calulation in its own variable (which I would prefer to do as it makes for more readable code).

The solution is only 1 line longer than your original solution and it does not rely on string concatenation (which is slow). It also simply does the formula work itself instead of setting the cell's value instead of setting the formula then having Excel calculate the value from said formula.

NOTE: If you wanted the formula visible in Excel (instead of just its value), I would think your original solution (with a couple tweaks) is sufficient for you needs:

Sub Main()
    'Use a more descriptive variable name than "s". Descriptive names improve
    'readability and better facilitates user understanding.
    Dim inflation_string as String

    'Using the for each syntax creates more readable code. Plus, as a bonus,
    'it removed the use of the index variable i.    
    For Each cell In Range("D6:L6")
        inflation_string = inflation_string + "*(" + Cells(8, cell.Column).Address + "+1)"
        cell.Formula = "=-$B$12*" + Cells(4, cell.Column).Address + inflation_string
    Next

End Sub

StackExchange Code Review Q#49235, answer score: 8