VBA sum on variable range

[chasoe]

Dear Experts,

I've a problem to sum up a variable range of cells, and would like your advice on how to modify the following codes.

Thx so much.

Edward

Dim SumFirstCell
Dim SumLastCell

Application.Goto Reference:="TotalToDate"

ActiveCell.Offset(1, 3).Range("A1").Select
SumFirstCell = ActiveCell.Address
Selection.End(xlToRight).Select
SumLastCell = ActiveCell.Address
 
Application.Goto Reference:="TotalToDate"
 
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=+SUM(SumFirstCell:SumLastCell)"

 

[AlphaFrog]

Try this...

[color=darkblue]With[/color] Range("TotalToDate").Offset(1)
    .Formula = "=SUM(" & .Offset(, 3).Address & ":" & .Offset(, 3).End(xlToRight).Address & ")"
End [color=darkblue]With[/color]

 

[chasoe]

Thx!

It works, but the sum (to the right) will become absolute, which I wish to copy the summation formula down.

And I don't know how to sum (upwards) at bottom, because recording macro will always give fixed cell address.

 

[AlphaFrog]

Thx!

It works, but the sum (to the right) will become absolute, which I wish to copy the summation formula down.

And I don't know how to sum (upwards) at bottom, because recording macro will always give fixed cell address.

[color=darkblue]With[/color] Range("TotalToDate")
    .Parent.Range(.Offset(1), .Parent.Cells(Rows.Count, .Column).End(xlUp)).Formula = "=SUM(" & .Offset(, 3).Address(0, 0) & ":" & .Offset(, 3).End(xlToRight).Address(0, 0) & ")"
[color=darkblue]End[/color] [color=darkblue]With[/color]

 

[chasoe]

The sum formula (to the right) is OK, but the TotalToDate cell is also over-writtten by the same formula.

I cannot make out the syntax to further amend.

Would you mind revising your codes, by ignoring the named cell "TotalToDate", but just give a summation formula at current cell by summing from next 3 column to xlToRight ? (ie no need to offset one row below TotalToDate). I also want to give a summation total by skipping one row below the range of rows above.

Thx again.

 

[AlphaFrog]

The sum formula (to the right) is OK, but the TotalToDate cell is also over-writtten by the same formula.

I cannot make out the syntax to further amend.

Would you mind revising your codes, by ignoring the named cell "TotalToDate", but just give a summation formula at current cell by summing from next 3 column to xlToRight ? (ie no need to offset one row below TotalToDate). I also want to give a summation total by skipping one row below the range of rows above.

Thx again.

Perhaps you could give an example using cell addresses?

What cell address is "TotalToDate"?
What range address do you want summed?
What range address do the formulas go?

 

[chasoe]

 

[chasoe]

Please refer image link

I would like to sum to right Q3 to Q8, and sum up at Q10. Thx

 

[AlphaFrog]

Application.Goto Reference:="TotalToDate"
Range("Q3:Q10").Formula = "=SUM(" & Range("T3", Range("T3").End(xlToRight)).Address(0, 0) & ")"
Range("Q12").Formula = "=SUM(Q3:Q10)"

 

[chasoe]

I'm sorry to have you mistaken, the steps should be

1. Go to cell named "TotalToDate" (which is a dynamic cell address depending on how many rows of detail transactions above the image portion), but somehow I can give the cell a name called "TotalToDate".
2. Go down one row (relatively)
3. Sum up to the right [SumR]- from 3 columns after (with dynamic columns of months to the right) [should be not summing absolute address)
4. Copy this summation formula down - with dynamic number of rows below, which is increasing each month
5. At bottom, skip one blank row, add a grand total formula at cell with double-underline which is also dynamic cell (sum up for column Q) on the above sums [SumR].

Hope you understand my requirements.

Thx so much.