Formula using a SUM of 3 code-defined cells
 

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

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

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

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

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

and (3)

CODE
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)

CODE
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 acros
12 columns.

how can I acheive this?

thanks in advance..

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=48385

Formula using a SUM of 3 code-defined cells
 

Dim rng1 as range, rng2 as Range, rng3 as Range, rng4 as Range
set rng1 = Selection.Find(What:="Committed Allocations Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Set rng2 = Selection.Find(What:="Committed Projects Total", _
After:=ActiveCell)
set rng3 = selection.Find(What:="Very Likley Projects Total", _
After:=ActiveCell)
set rng4 = Selection.Find(What:="Unplanned Very Likely Total", _
After:=ActiveCell)
if not rng1 is nothing then set rng1 = rng1.offset(0,2)
if not rng2 is nothing then set rng2 = rng2.offset(0,2)
if not rng3 is nothing then set rng3 = rng3.offset(0,2)
if not rng3 is nothing then set rng4 = rng4.offset(0,2)

ActiveCell.Offset(0,2).Resize(1,12).Formula = _
"=" & rng1.Address(1,0) & "+" & _
rng2.Address(1,0) & "+" & _
rng3.Address(1,0) & "+" & _
rng4.Address(1,0)

--
Regards,
Tom Ogilvy


"matpj" wrote in
message ...

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)

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

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

and (3)

CODE
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)

CODE
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...


--
matpj
------------------------------------------------------------------------
matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=483858