Automate formulated rows based on user input

OK, try this,

Click the calculate button and enter the amount of rows you want to calculate, eneter the amount, enter the duration in the prompt boxes.

Option Explicit

Sub d()
  Dim lr As String, mBox1 As String, mBox2 As String
  Dim i&, k&, lr1&
  
  Application.ScreenUpdating = 0
    lr1 = Cells(Rows.Count, 5).End(xlUp).Row
    Range(Cells(4, 2), Cells(lr1, 8)).ClearContents
    
    mBox1 = InputBox("Enter Total cost", "Total Cost")
    lr = InputBox("Enter Duration", "Duration")
   
       Range("F2").Value = mBox1
       Range("K2").Value = lr
    
            For i = 4 To lr + 3
                Cells(i, 3).FormulaR1C1 = "=NORMDIST(RC[-1],(R2C11/2),R2C[6],0)"
                Cells(i, 4).FormulaR1C1 = "=RC[-1]*R2C[2]"
                Cells(i, 5).FormulaR1C1 = "=RC[-1]/R2C6"
                  k = k + 1
                Cells(i, 2).Value = k
            Next i
            
        lr = lr + 4
       Cells(lr + 2, 5).FormulaR1C1 = "=SUM(R[" & -lr + 2 & "]C:R[-2]C)"
       Cells(lr + 4, 5).Value = "Check Sum"
       Cells(lr + 4, 6).FormulaR1C1 = "=SUM(R[" & -lr & "]C[-2]:R[-2]C[-2])"
       Cells(lr + 4, 8).FormulaR1C1 = "=(R2C6-RC[-2])/R2C6"
        
    Application.ScreenUpdating = 1
  
End Sub

See if this does what you are after?

I am not sure I follow. What do you mean by the calculate field? Are talking about the input box where you enter a value when prompted? What is sheet3 for? I am sure there is a solution, so don't worry.

OK, Give this a whirl.

define type as static if you want to preserve the value of the last user inputs

JieJenn, it does not have to have variable defined as a static variable. If the value was being cleared, then yes I would say use one, but in this case the value remains in the cell so referring to the cell achieves the same result. Just my thoughts on the example, I am open to other opinions however.