set sum formula in excel using vba

Question

Working on a excel macros where I am trying to add the cell values from above cells to calculate total value. This is what my data looks like

Here I want to add above cell values for each column to calculate the sum. To accomplish this i have written a macro as follows.

For cl = 2 To 5    
    Worksheets(5).Cells(4, cl).Formula = "=SUM(B4:B6)"        
Next cl

This should set the formula to each cell in a row till 5 columns. But it sets the same formula on all cells in a row it should get change according to column. How to set sum formula for each cell for corresponding column ?

Answer 1

Not sure I quite understand your code. You seem to be writing into row 4, but you also want to sum from row 4 to row 6. That will create a circular reference.

Let's assume the formula is written into row 3 instead. You will want to use the R1C1 reference style to make the cells to sum relative to the current cells.

A trick to learn what reference to use is:

• Get a new worksheet, enter =SUM(B4:B6) into cell B3 and copy to the right.
• Then click File > Options > Formulas and select R1C1 Reference Style.
• Now inspect the formulas in the sheet. You will see this: =SUM(R[1]C:R[3]C)

This is what you need in the macro.

For cl = 2 To 5    
    Worksheets(5).Cells(3, cl).FormulaR1C1 = "=SUM(R[1]C:R[3]C)"    
Next cl