Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

sum formula

  Asked By: Boell    Date: Dec 31    Category: MS Office    Views: 992
  

I need to be able to sum across columns. The sum formula needs to be
entered using code and not always entered. For example:

=sum(d4:g4)
=sum(d5:g5)
=sum(d6:g6)
no formula
=sum(d8:g8)
etc.

The next time the report is created, the formula would be in different
cells. I know how to get the formula into the cell just not how to get
into the cells on the fly.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Elizabeth Hughes     Answered On: Dec 31


To put a formula  =sum(D8:G8) into cell  H8

You would use Activesheet.range("H8").formula = "=sum(D8:G8)"

 
Answer #2    Answered By: Arlene Harvey     Answered On: Dec 31

I need to know how to check to see if the row has
something entered. If it does, enter the formula. I
need to the row to change as I go. Is there a way to
do this??

 
Answer #3    Answered By: Caroline Bowman     Answered On: Dec 31

I think I figured out what will work:

drow = 4
dcol = 8
Sheets("1-4 Wks").Cells(drow, dcol).Formula =
"=sum(d4:g4)"

I have looked for something like this for a long time.
Finally, I stumbled onto it.

 
Answer #4    Answered By: Karla Ortiz     Answered On: Dec 31

The nicest way to put formulas like this into cells  is to use R1C1
addressing mode.

This works because you want to keep the sum  on the same row as the formula  -
wherever that is - and R1C1 does this neatly. There are two alternatives.
Absolute:

xx.FormulaR1C1 = "=sum(RC4:RC7)"

(where xx is the H cell  you want the formula in). Sums columns  4 to 7 in
the current row. Or relative:

xx.FormulaR1C1 = "=sum(RC[-4]:RC[-1])"

Sums the columns 4 to the left through to 1 to the left. When put into an H
cell, both will do the same thing.

The good thing about the R1C1 way of doing it is that the formula doesn't
need to be reworked to match the row you're putting it on. For instance, I
attached this to a command button

Option Explicit

Private Sub CommandButton1_Click()
Range("h3").FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Range("h8").FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Range("h10").FormulaR1C1 = "=sum(RC4:RC7)"
Range("h15").FormulaR1C1 = "=sum(RC4:RC7)"
End Sub

Note that the formulas are the same no matter which row they're going in.
(The ones for 3 and 8 are the relative form, and the other two are the
absolute form. In practice, you'd choose one form and use it for all the
formulas.)

 
Answer #5    Answered By: Anu K     Answered On: Dec 31

The nicest way to put formulas like this into cells  is to use R1C1
addressing mode.

This works because you want to keep the sum  on the same row as the formula  -
wherever that is - and R1C1 does this neatly. There are two alternatives.
Absolute:

xx.FormulaR1C1 = "=sum(RC4:RC7)"

(where xx is the H cell  you want the formula in). Sums columns  4 to 7 in
the current row. Or relative:

xx.FormulaR1C1 = "=sum(RC[-4]:RC[-1])"

Sums the columns 4 to the left through to 1 to the left. When put into an H
cell, both will do the same thing.

The good thing about the R1C1 way of doing it is that the formula doesn't
need to be reworked to match the row you're putting it on. For instance, I
attached this to a command button

Option Explicit

Private Sub CommandButton1_Click()
Range("h3").FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Range("h8").FormulaR1C1 = "=sum(RC[-4]:RC[-1])"
Range("h10").FormulaR1C1 = "=sum(RC4:RC7)"
Range("h15").FormulaR1C1 = "=sum(RC4:RC7)"
End Sub

Note that the formulas are the same no matter which row they're going in.
(The ones for 3 and 8 are the relative form, and the other two are the
absolute form. In practice, you'd choose one form and use it for all the
formulas.)

 
Answer #6    Answered By: Fjodor Bonkob     Answered On: Dec 31

Hopefully, I'll be as smart as you
and won't have to bug everyone for help.

 
Didn't find what you were looking for? Find more on sum formula Or get search suggestion and latest updates.




Tagged: