Refreshing formulas via VBA
 

This is my first post to this forum so apologies if this is one of thos
questions that get asked 100s of times.

I am using VBA to ask user various questions which results in th
automatic generation of product quotation (costs, discounts etc).

largely it is working fine but I am having trouble calculating totals.

I use a template sheet which is copied I then INSERTROWs into the cop
based on the users responses.

In the template I have various rows detailing sub-totals / totals.

As rows are inserted the SUM function does not necessaril
automatically update itself. i.e. the SUM may start life as SUM (E1
(an arbitrary blank cell). After I have inserted X number of rows
expected the SUM function to refresh itself to say SUM(E1:E10).

Is there a better way of doing this - Am I expecting too much

--
longy
-----------------------------------------------------------------------
longyp's Profile: http://www.excelforum.com/member.php...fo&userid=3104
View this thread: http://www.excelforum.com/showthread.php?threadid=50716

Refreshing formulas via VBA
 

I like to put my sum/subtotals in row 1. Then I can use:

=sum(a3:A65536)
and not have to worry about where I inserted rows or adjusting the formula.

But if you're formula is at the bottom, you could use a formula like this:

This formula is in A25 and sums A3:A24:
=sum(A3:offset(a25,-1,0))



longyp wrote:

This is my first post to this forum so apologies if this is one of those
questions that get asked 100s of times.

I am using VBA to ask user various questions which results in the
automatic generation of product quotation (costs, discounts etc).

largely it is working fine but I am having trouble calculating totals.

I use a template sheet which is copied I then INSERTROWs into the copy
based on the users responses.

In the template I have various rows detailing sub-totals / totals.

As rows are inserted the SUM function does not necessarily
automatically update itself. i.e. the SUM may start life as SUM (E1)
(an arbitrary blank cell). After I have inserted X number of rows I
expected the SUM function to refresh itself to say SUM(E1:E10).

Is there a better way of doing this - Am I expecting too much?

--
longyp
------------------------------------------------------------------------
longyp's Profile: http://www.excelforum.com/member.php...o&userid=31044
View this thread: http://www.excelforum.com/showthread...hreadid=507169

--

Dave Peterson

Refreshing formulas via VBA
 

Hi Longyp,

No need for VBA ...
Why don't you just name the first cell E1 and your current last cell
(with Insert Name Define ) with names such as 'Start' and 'End' , and
have your sum formula look like : sum(Start:End)
From then, all rows inserted in between will be included in your total
....
HTH
Cheers
Carim

Refreshing formulas via VBA
 

Thanks People.

I like the naming idea start:end.


--
longyp
------------------------------------------------------------------------
longyp's Profile: http://www.excelforum.com/member.php...o&userid=31044
View this thread: http://www.excelforum.com/showthread...hreadid=507169