inserting row...but the formula is not affected
 

I am inserting a row by Rows(fndVal1.Row).Insert. Now i
have formulas like =SUM(J7:J51) in rows after the new row
i insert. Now saw i insert a row at 50th....row. nowmally
if u insert a row manualy in excel automatically the
formula changes from =SUM(J7:J51) to =SUM(J7:J52)..as a
new is inserted. But when i am doing so by coding the
formula in my sheet is not changing.is it practically
possible? if yes what do i need to do!

thanks a lot
monika

inserting row...but the formula is not affected
 

I think you will find that whether you insert manually or with code the
action is the same. If you insert a row where the SUM formulas are,
they do not change.

The usual trick is to have a blank row above the formulas, when adding
rows will change the formulas.


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

inserting row...but the formula is not affected
 

Hi Monika,
Instead of coding for cell J52: as
=SUM(J7:J51)
code as
=SUM(J$7:OFFSET(J52,-1,0))
then you can insert a row immediately before your total row
(row 52) with SUM.

For more information on inserting rows and the use of
OFFSET see
http://www.mvps.org/dmcritchie/excel...row.htm#offset
then there is not need to code a blank row above your
total and for which you have no assurance that someone
would not put an amount on that blank row.
---
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

From: "monika"
Newsgroups: microsoft.public.excel.programming
Sent: Monday, April 19, 2004 04:57 AM
Subject: inserting row...but the formula is not affected


I am inserting a row by Rows(fndVal1.Row).Insert. Now i
have formulas like =SUM(J7:J51) in rows after the new row [clipped]