Creatign a SUM formula with 3 'found' cells

[t16turbo]

I have a total line I have inserted into my spreadsheet.

The activecell in column A is offset by (0,2) to reach the destination for the formula.

In that active cell I need to SUM the values of the cells referenced by
(1)

Selection.Find(What:="Committed Allocations Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(0, 2).Activate

and (2)

Selection.Find(What:="Committed Projects Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(0, 2).Activate

and (3)

Selection.Find(What:="Very Likley Projects Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(0, 2).Activate

and (4)

Selection.Find(What:="Unplanned Very Likely Total", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(0, 2).Activate

so I need to have the sum of (1)+(2)+(3)+(4) and then fill this across 12 columns.

how can I acheive this?

thanks in advance...

 

[Gavona]

Is their only one instance of each of your Find values?
If so then after each bit of your code add the line:
MyAddress1=Selection.address
Then Range(whatever).formula="sum(" + MyAddress1 + ";" + MyAddress2..............

If their are multiple subtotals then it occurs to me that you could:
Select all the cells in the first sub-total column
use a filter to display only the rows with subtotals (with advanced filter a single operation could display all meeting any of the 4 criteria)
Select visible cells only
Use that address in a sum formula
Copy across to relevant rows

Hope that helps


Gavin

 

[t16turbo]

Hi gavin,

the totals I am adding are a kind of sub-total.
i've basically inserted a rows in the middle of an existing table.
I then need to total up some totals ABOVE that inserted row.
using filters etc might not behave very well.

there is one instance of each of the search criteria in that code.
I have also tried setting a variable to the activecell.value
which works in the first 'Total' column, but if it's copied across, it keeps the value of the variables (as the code that sets the variables is a reference to one cell)

I need that reference to increment as it is filled.

 

[Gavona]

Not sure I fully understand.
Maybe if you posted the formula your code produces in the first column, the formula that you want it to produce and even the code that produces the formula....

ActiveCell.Address not ActiveCell.value?

Clarification:
1. It sounds to me as if you have 4 existing TOTAL rows. The first instance of the totals is in column C with similar totals in the next 11 columns.
Are you trying to construct the formulae for the Total rows or are they already in place and you are just trying to add the 4 totals in each column to get a grand total?

2. You can find each Total Row using the Find method you have posted. (ActiveCcell.row or ActiveCell.address ought to be useful in this)

3.You have not said where you want the totals to appear but one presumes above or below the data as you want to drag across.
Presumably the table is refreshed regularly with different numbers of rows etc. In that case easiest to have the totals above the data so they are in a fixed position.

4.Possible problem re-definition?
Is all the data in Column C summarised into one of the existing Totals? Could your challenge be re-defined as Total the whole of Column C excluding the 4 existing totals?
If so we just convert existing Sum formulae to subtotal formulae (using code / edit and replace) and grand totals would be a doddle.


Gavin

 

[SkipVought]

Hi,

This COULD have been a job for...

the SUBTOTAL Wizard -- Data/Subtotal...

However, you don't have

Committed Allocations, etc on each row of your table.

YOU COULD!

...and this whole thing could be done in about 10 seconds.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}