Dynamic sum

[MikeF]

Need to accomplish a "dynamic sum".

When my sub runs, it constructs one column that is sometimes 5 rows
[starting at row 2 always], sometimes 27 rows, sometimes 156 rows, and so on,
depending on other parameters.

Navigating to row 1, then wherever end(xlDown).offset(1,0).select ends up
is where it needs to sum, from whatever row that is [ie 6, 28, 157, or
whatever] up thru row 2 in the same column.

In other words, the active cell simply goes to the bottom of the column and
needs to sum everything above itself, which is a different number of rows all
the time.

Have tried numerous relative reference routines, and just can't seem to get
it.

Any ideas will be greatly appreciated.

Thanx,
- Mike

 

[Tom Hutchins]

Try

ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

where ActiveCell is the cell to receive the SUM formula. Sums row 2 through
the cell above ActiveCell in that column.

Hope this helps,

Hutch

 

[MikeF]

Thanx.
Must've been at this too long today.

.... Slapping my forehead.

- Mike

Try

ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

where ActiveCell is the cell to receive the SUM formula. Sums row 2 through
the cell above ActiveCell in that column.

Hope this helps,

Hutch

Need to accomplish a "dynamic sum".

When my sub runs, it constructs one column that is sometimes 5 rows
[starting at row 2 always], sometimes 27 rows, sometimes 156 rows, and so on,
depending on other parameters.

Navigating to row 1, then wherever end(xlDown).offset(1,0).select ends up
is where it needs to sum, from whatever row that is [ie 6, 28, 157, or
whatever] up thru row 2 in the same column.

In other words, the active cell simply goes to the bottom of the column and
needs to sum everything above itself, which is a different number of rows all
the time.

Have tried numerous relative reference routines, and just can't seem to get
it.

Any ideas will be greatly appreciated.

Thanx,
- Mike