PDA

View Full Version : [SOLVED] Sum a range across multiple Worksheets



simora
08-04-2015, 03:06 PM
I have a sheet called "Totals" which sums all the worksheet totals for each cell between the sheets called "FIRST" and "LAST"

The formula that goes into each cell is like

=SUM(First:Last!D3)


Now what I want is to put the formula into each cell in a range so that it totals all the appropriate cells.

How can I re-write it in VBA so that if a cell's calculated value is 0 or nothing it just gives me a blank " " else it calculates the cell's value.

This works like this

Range("D3:E666").Formula = "=Sum(First:Last!D3)"


But when I try to do this to account for the cell's calculated value being zero


Range("D3:E666").Formula = "=IF(SUM(First:Last!D3)=0,"",=SUM(First:Last!D3))"


I get an error.

Trebor76
08-04-2015, 04:13 PM
Hi simora,

Try this:


Range("D3:E666").Formula = "=IF(SUM(First:Last!D3)=0,"""",SUM(First:Last!D3))"

Regards,

Robert

simora
08-04-2015, 05:18 PM
Thanks Robert.

It worked perfectly.

How would I re-write this .Formula = "=Sum(First:Last!D3)" So that the D3 refrences the ActiveCell in a Specific range.

This will be going into a loop on another worksheet.

Trebor76
08-04-2015, 05:43 PM
I'm not really sure what you mean, but as a start this will put $B$2 into the formula:


.Formula = "=Sum(First:Last!" & Range("B2").Address & ")"

Does that help?

Robert

simora
08-04-2015, 07:32 PM
Thanks Robert.

I got it to work like this


Cell.Formula = "=Sum(First:Last!" & Cell.Address & ")"

Really Appreciated your help on this.

Trebor76
08-04-2015, 09:15 PM
You're welcome :)