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 Sample Excel Document

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 ?

brettdj · Accepted Answer · 2013-07-27 11:14:45Z

9

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

edited Jul 27, 2013 at 11:14

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered Jul 25, 2013 at 8:14

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Geographos · Accepted Answer · 2018-08-17 13:44:10Z

2

You can use very simple formula like below:

Sub sum_month()
Sheets("13").Activate
Range("D2").Formula = "=SUM(A1+A2+A3)"
End Sub

And next just hold and drag the cell to fill up another rows automatically.

answered Aug 17, 2018 at 13:44

Geographos

1,5962 gold badges36 silver badges86 bronze badges

Comments

Serafin William · Accepted Answer · 2018-12-11 03:49:34Z

2

For your case, you can use this:

For cl = 2 To 5
    ColName = Left(Cells(1, cl).Address(0, 0), (Cells(1, cl).Column < 27) + 2)
    ValueSum = "=SUM(" & ColName & "4:" & ColName & "6)"
    Worksheets(5).Cells(4, cl).Formula = ValueSum
Next

answered Dec 11, 2018 at 3:49

Serafin William

19411 bronze badges

Comments

Stefan · Accepted Answer · 2016-09-21 13:52:46Z

1

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)"

answered Sep 21, 2016 at 13:52

Stefan

3842 silver badges19 bronze badges

Comments

wattostudios · Accepted Answer · 2014-03-15 00:54:36Z

0

Try something like this.

For cl = 2 To 5
    ColName = Left(Cells(1, cl).Address(False, False), 1 - (cl > 26))
    Worksheets(5).Cells(4, cl).Formula = "=SUM(" & ColName & "4:" & ColName & "6)"
Next cl

edited Mar 15, 2014 at 0:54

wattostudios

8,78013 gold badges45 silver badges58 bronze badges

answered Jul 25, 2013 at 8:05

user1685490

512 bronze badges

Comments

wallyeye · Accepted Answer · 2018-12-11 05:53:52Z

0

I think you can just reference the whole formula range and Excel will be smart enough to adjust the columns.

With no loop:

Worksheets(5).Cells(4, cl).resize(1,4).Formula = "=SUM(B4:B6)"

answered Dec 11, 2018 at 5:53

wallyeye

1165 bronze badges

Collectives™ on Stack Overflow

set sum formula in excel using vba

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related