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

Answer 2

some more details to R1C1 and R[1]C[1] style in formulas. As far as I know R[1]C[1] creates a relative reference and R1C1 creates a absolute reference. But keep in mind that the figures for R[x] and C[y] are an offset to the cell which contains the formula.

That means if you want to show the sum of A1:B4 in C5 the code has to be like this:

Worksheets(5).Cells(5, 3).FormulaR1C1 = "=SUM(R[-4]C[-2]:R[-1]C[-1])"   

If you want to the same but ending up with an absolute reference is aht to look like this.

 Worksheets(5).Cells(5, 3).FormulaR1C1 = "=SUM(R1C1:R4C2)"