Refreshing formulas via VBA

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?

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

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

Thanks People.

I like the naming idea start:end.