Updating the formula by program

[sameerce]

Hi,

I have developed an Excel VBA based application. I want one feature i
the application such that to the existing formula value of one mor
cell gets added on a button click.
For example, I have a formula for Cell 1 as "=C2+C3+C4".
I have a button placed below it which when presses once converts thi
formula to "=C2+C3+C4+C5".

Please tell me if this is possible to be done programatically.

Thanks,
Samee

 

[Frank Kabel]

Hi
try something like
with activesheet.range("A1")
..formula=.formula & "+C5"
end with

 

[sameerce]

No Frank, the formula you suggested is not working.

Any other suggestions

 

[Frank Kabel]

Hi
I added one dot too much. try:
with activesheet.range("A1")
..formula=.formula & "+C5"
end with

If this does not work please describe exactly what is not
working!

 

[Tom Ogilvy]

On the off hand chance you might want to press the button more than once,
this might be a possibility:

Sub AddNextRow()
Dim rng As Range, rng1 As Range
Dim rng2 As Range
Set rng = Range("B9")
On Error GoTo ErrHandler
Set rng1 = rng.DirectPrecedents
Set rng2 = rng1.Areas(rng1.Areas.Count)
Set rng2 = rng2(rng2.Count)(2)
rng.Formula = rng.Formula & "+" & rng2.Address(0, 0)
ErrHandler:
End Sub

 

[sameerce]

Thanks a ton to all. It worked

 

[David McRitchie]

Do you really need such a button, can't you just use the
SUM Worksheet Function with a range.

Aren't there better solutions:
Use of OFFSET allows you to insert and delete rows
right up to inserting a line before the SUM formula.
A30: =SUM(A2:OFFSET(A30,-1,0))
would sum numbers from A2:A29

You can use the SUM button, which would, I think,
be preferable to your request but it does have a problem
if you insert after or delete the last reference in the range.

By adding in cells one by one you have the advantage
or disadvantage of getting a #Value! if a cell contains text.
But you should be aware that there is a specification limit as to the
size of a formula.
Arguments in a function 30
Length of formula contents 1,024 characters
The second one could be a problem for you, as you could only
reach up to 228 cells to be added from a column, if
you include all of the individual addresses.

i.e.
A229: =SUM(A2:OFFSET(A227,-1,0))
as opposed to your
A229: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+ ... + A228)

Hope you are not using this for a running total,
which could take outlandish calculation times by adding up the same
numbers over an over as opposed to the previous running total + increment.

 

[David McRitchie]

I botched the end of the post A1 to A226 is the limit if
you try to include them as =A1+A2+A3+ ... + A226
instead of =SUM(A1:A226)

so the formulas that I started to correct but didn't finish should read
i.e.
A227: =SUM(A2:OFFSET(A227,-1,0))
as opposed to your
A227: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+ ... + A226)