Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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??

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.

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

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

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.

Related Topics:

- clearing cells value without deleting the formula I've made in cell from excel formula , HOW?
- VBA code to sum
- sum in columns
- Summing on varying number of rows in Excel using VBA
- Loop in formula
- Copy down formula with vba
- Array Formula
- Ctrl+Shift+Arrow Fails To Select Cells When Used in Formula Bar
- Summing a range
- getting the sum of the last 20 cells
- Stuck on copying formula from 1 sheet to another
- Named formula/Windows dialog boxes
- Conditional Summing
- cells containing formula that refer to user-defined VBA function
- Calculate Total Sum
- Sum colunm total in repeater
- sum of amount deliverd group
- count the sum of a column
- VBA Formula prob
- SUM content of rows
- Getting a formula to copy from inside VBA
- custom Sum function?
- Populate Formula(Function) in rows
- searching within formulas
- Summing a range of variable size