Updating the formula by program
 

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

--
Message posted from http://www.ExcelForum.com

Updating the formula by program
 

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

-----Original Message-----
Hi,

I have developed an Excel VBA based application. I want
one feature in
the application such that to the existing formula value
of one more
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 this
formula to "=C2+C3+C4+C5".

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

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/

.

Updating the formula by program
 

No Frank, the formula you suggested is not working.

Any other suggestions

--
Message posted from http://www.ExcelForum.com

Updating the formula by program
 

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!

-----Original Message-----
No Frank, the formula you suggested is not working.

Any other suggestions?


---
Message posted from http://www.ExcelForum.com/

.

Updating the formula by program
 

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


--
Regards,
Tom Ogilvy

"sameerce " wrote in message
...
No Frank, the formula you suggested is not working.

Any other suggestions?


---
Message posted from http://www.ExcelForum.com/

Updating the formula by program
 

Thanks a ton to all. It worked

--
Message posted from http://www.ExcelForum.com

Updating the formula by program
 

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"sameerce " wrote in message ...
Thanks a ton to all. It worked.


---
Message posted from http://www.ExcelForum.com/

Updating the formula by program
 

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)