Adding loops to a formula and displaying only the values of the formula computation

Question

I have two issues that I would appreciate a lot of help on:

1. I have VBA linked to a button which computes values on some cells based on the inputted parameters. I am still a basic user however and could not get it to loop. The intention is for the VBA to continue to calculate until the value in a given cell zero.

2. Is it possible to have VBA to compute input values only into the cell without inputting the corresponding formula? See a snippet of the code below:

   Sub Formular1() ' Formular1

   Range("H17").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(1, 0).Range("A1").Select
   ActiveCell.FormulaR1C1 = "=ROUNDDOWN((RC[-1]-RC[-2])/30,0)"
   ActiveCell.Select
   
   ' Formular2 Macro
   Range("I17").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(1, 0).Range("A1").Select
   ActiveCell.FormulaR1C1 = "=RC[-5]/RC[-1]"
   ActiveCell.Offset(1, 0).Range("A1").Select
   
   ' Formular 3
   Range("J17").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(1, 0).Range("A1").Select
   ActiveCell.FormulaR1C1 = "=R4C9"
   Range("J20").Select
   
   End Sub
   

Answer 1

You can either write the formula into the cell and immediately revert the formula to its returned value or calculate the value and write it into the cell directly.

dim nr as long
'option 1
with activesheet
    nr = .cells(rows.count, "H").end(xlup).offset(1, 0).row
    .cells(nr, "H").formula = "=TRUNC((G" & nr & "-F" & nr & ")/30)"
    .cells(nr, "I").formula = "=G" & nr & "/F" & nr
    .cells(nr, "J").formula = "=I4"
    with .cells(nr, "H").resize(1, 3)
        .value = .value
    end with
end with

'option 2
with activesheet
    nr = .cells(rows.count, "H").end(xlup).offset(1, 0).row
    .cells(nr, "H") = int((.cells(nr, "D").value - .cells(nr, "H").value) / 30)
    .cells(nr, "I") = cdbl(.cells(nr, "G").value / .cells(nr, "F").value)
    .cells(nr, "J") = .cells(4, "I").value
end with

There you have examples of each method. I did simplify your first formula a bit by using the TRUNC function in place of the ROUNDDOWN function but they both perform the same operation in this case.

I am a little concerned that you seem to be writing these formula/values into a new row but you are referencing other cells in the same row. I would have to suppose that these other cells have been populated with formulas or values first.