Make Excel Formula auto-add itself when new rows added

0
votes

I have added a User Form to an excel spreadsheet that asks a user a question and adds it to a new row in a spreadsheet.

Each column in the spreadsheet has its own formula that calculates based off user input.

Is there a way that I can have excel automatically "pull" the formula that is contiguous in the column into the added row?

I imagine that I can change it in the code for my User Form, so when a person hits "Submit" all the cells update. But I only know how to update the value, not add a formula in place. Here is part of the code I have:

    .Cells(iRow, 6).Value = Me.TextBox1.Value
    .Cells(iRow, 7).Value = Me.TextBox2.Value
    .Cells(iRow, 8).Value = Me.TextBox3.Value
    .Cells(iRow, 9).Value = Me.TextBox4.Value
    .Cells(iRow, 10).Value = Me.TextBox5.Value
    .Cells(iRow, 11).Value = Me.TextBox6.Value
    .Cells(iRow, 12).Value = Me.TextBox7.Value
    .Cells(iRow, 13).Value = Me.TextBox8.Value
    .Cells(iRow, 14).Value = Me.TextBox9.Value
    .Cells(iRow, 15).Value = Me.TextBox10.Value

Is it possible to add something to change the cell values 1-4? Maybe like this:

.Cells(iRow, 1).Value = *insert formula here*
2
vbaexcel

2 Answers

1
votes

You can use the range relative formula property .FormulaR1C1 to make formula references adjust relatively:

.Cells(iRow, 1).FormulaR1C1 = .Cells(iRow - 1, 1).FormulaR1C1

You can also use the regular .Formula properties instead, if you want the exact same formula, without relative references updating.

You can also do it for all 4 columns at once:

.Cells(iRow, 1).Resize(, 4).FormulaR1C1 = .Cells(iRow - 1, 1).Resize(, 4).FormulaR1C1
0
votes

If the formula is already in the cell above, you can do the below:

.Range(.Cells(iRow - 1, 1).Address,.Cells(iRow, 1).Address).Filldown

This will fill the formula from the cell above to the cell in question.