Varying ranges in macro formula

[Deborah Nunn]

I am trying to create a macro which will sum the figures
in the adjacent column. The problem I run into is that
the range for the numbers varies from one sum to the
next. When I type in the formula, I use ctrl+shift+up
arrow, but the formula records the number of rows up or
down. If anyone can help me, please send me an e-mail.
This is the first time I've posted to a newsgroupo in ten
years.

Deborah

 

[hgrove]

Deborah Nunn wrote...

I am trying to create a macro which will sum the figures in the
adjacent column. The problem I run into is that the range for
the numbers varies from one sum to the next. When I type in
the formula, I use ctrl+shift+up arrow, but the formula records
the number of rows up or down. If anyone can help me, please
send me an e-mail. This is the first time I've posted to a
newsgroupo in ten years.

Sorry, can't e-mail. Besides, post here, read here.

If you want the sum from the ActiveCell's row *up* to the top of th
filled range in the column to the left of the ActiveCell, you coul
use

sumval = Application.WorksheetFunction.Sum( _
Range(ActiveCell.Offset(0, -1).End(xlUp), ActiveCell.Offset(0, -1)) _
)

If you want your macro to enter the formula for you, try

ActiveCell.Formula = "=SUM(" & _
Range(ActiveCell.Offset(0, -1).End(xlUp), _
ActiveCell.Offset(0, -1)).Address(0, 0) & ")