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:

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.

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

score 0 · Accepted Answer · answered Sep 20 '15 at 06:18

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.

I just updated and it worked like magic. Thank you. However, i want to make some changes and i don't want to destroy what you have done. I would like to change the relative cell selection at the beginning of the code to absolute and i would like to add a loop. Firstly, I want the computations to always start from row 19 and to end on the row where column G does not have any value. Thanks again. Could you please add comments to your codes so that i can at least learn and improve? — Avictomama, Sep 20 '15 at 17:57

score -2 · Answer 2 · answered Sep 20 '15 at 14:03

not exactly sure what you are trying to do. but

obviously I am not using your range exactly because you did not provide enough information about what you want to do.

if you want to go through a range of cells and convert formulas into values, it would be something like this.

for each rcell in worksheet("myworksheet").Range("A1:A15").Cells
if rcell.value = "0" then
exit sub
else
rcell.value = rcell.value
end if
next rcell

By the way, a friend told me about this site he used. he said they provide a few free training videos on basic excel formulas no vba yet, but that he used them to create a macro for him and it was a very reasonable cost.

http://www.excelformulashelp.com/

Thanks for your response. I think jeeped has done justice already to the question. But could you please proffer a solution to loop question? — Avictomama, Sep 21 '15 at 07:59

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

2 Answers2