Populate columns with formula automatically

[deduwa]

I am looking for a code that does the following in a data set with headings & with a dynamic number of rows (Using existing data in column B to indicate where the last row is);

--- Cell range J2 to last row in column J to be populated with the formula =G2-I2 in row 2, =G3-I3 in row 3 all the way to the last row

--- Cell range M2 to last row in column M to be populated with the formula =if(K2="",G2,K2) in row 2, =if(K3="",G3,K3) in row 3 all the way to the last row

--- Cell range O2 to last row in column O to be populated with the formula =M2-I2 in row 2, =M3-I3 in row 3 all the way to the last row

--- Cell range P2 to last row in column P to be populated with the formula =SUM(E2:F2) in row 2, =SUM(E3:F3) in row 3 all the way to the last row

--- Cell range Q2 to last row in column Q to be populated with the formula =E2*O2/100 in row 2, =E3*O3/100 in row 3 all the way to the last row

--- Cell range R2 to last row in column R to be populated with the formula =F2*O2/100 in row 2, =F3*O3/100 in row 3 all the way to the last row

--- Cell range S2 to last row in column S to be populated with the formula =Q2+R2 in row 2, =Q3+R3 in row 3 all the way to the last row


Thank you

 

[My Aswer Is This]

Normally when using code we would enter the result of the formula into the cell not the formula.
If you want the formula why not enter the formula manually and then drag down

 

[Joe4]

Normally when using code we would enter the result of the formula into the cell not the formula.

Not necessarily. If the data can change, they may want it to be dynamic.

deduwa,
If you turn on the Macro Recorder, and record yourself entering all those formulas in row 2, you will get all the formulas you need.
Then, it is just a matter of changing the range on the left side of the formula to do all the rows instead of that one row, i.e.

So, if you recorded the first one, it will show you:

    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-1]"

You can combine these two rows to this:

    Range("J2").FormulaR1C1 = "=RC[-3]-RC[-1]"

Now, we can find the last row like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"B").End(xlUp).Row

So, now just change your range in your formula like this:

    Range("J2:J" & lastRow).FormulaR1C1 = "=RC[-3]-RC[-1]"

So, your code would start like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"B").End(xlUp).Row

Range("J2:J" & lastRow).FormulaR1C1 = "=RC[-3]-RC[-1]"

and then just continue on adding the other formulas underneath it.