vba excel - input a SUM formula into cell that sums cell values downwards until blank cell

[Pianostool]

Hi folks.

I have written some code to allow data input through a form. One of the fields is a $ amount.
Each time an entry gets added through the form, it adds a new row in at Row 7, so all other rows of prior data drop down below it.
I have a total of the $ column in cell C5, and some code that totals it all OK as rows get added, as part of the form code.
The $ values are in Column B and the new record gets added each time to Row 7. So the newest dollar value is at B7, and all other $ values from previous entries are below it in column B, but obviously the list grows.
So my code total in C5 works fine, giving me the correct answer, but it's just a figure (the code is doing the formula itself).
I'm wondering how I might get the SUM formula input into C5 via the code, so it's adding from B7 down to the first blank cell in column B?
I need it as a formula rather than a figure, as the users may change one of the $ values in column B later, manually, and this will not update the total if it's just a figure from the last form input run.
I hope that makes sense.
Thanks in advance.

 

[Peter_SSs]

@Peter_SSs - please note.

That doesn't make any difference to my suggestion does it?
Once the formula is initially set to add the top section, any new rows will be added at row 7 so the simple sum will not jump down and add anything from the next section.

 

[Pianostool]

If you are having AGGREGATE function

=SUM($B$7:INDEX($B$7:$B$2000,AGGREGATE(15,6,ROW($B$7:$B$2000)/($B$7:$B$2000=""),1)-ROW($B$7)))

else
Array formula

=SUM($B$7:INDEX($B$7:$B$2000,SMALL(IF($B$7:$B$2000="",ROW($B$7:$B$2000),""),1)-ROW($B$7)))

@kvsrinivasamurthy - Legend - thank you. The new Array formula now seems to work on both the Savings and Assets totals when I add a new row in via the form for either one.
I will mark it as the Solution.
Thanks again.

 

[Peter_SSs]

That doesn't make any difference to my suggestion does it?

If it does and you want to actually look for that first blank cell then I think all you need is this

=SUM(B6:INDEX(B6:B$2000,XMATCH(TRUE,B6:B$2000="")-1))

 

[Peter_SSs]

The new Array formula now seems to work on both the Savings and Assets totals

.. but it does need a different starting row still doesn't it?
(and it is considerably more complex that should be required for your Excel version)

 

[Pianostool]

That doesn't make any difference to my suggestion does it?
Once the formula is initially set to add the top section, any new rows will be added at row 7 so the simple sum will not jump down and add anything from the next section.

Hi Peter - unfortunately the simple formula moves down to the next row as the starting point for summing when a new row is added via the vba form.
All good, @kvsrinivasamurthy last Array formula has solved it.
Thanks for your time and help.

 

[Peter_SSs]

Hi Peter - unfortunately the simple formula moves down to the next row as the starting point for summing when a new row is added via the vba form.

So that I can understand that, can you spell it out a bit more for me?
=SUM(B6:B9)
(might not be B9 but whatever is the last row of that first section when the formula is first placed into C5)
This adds from row 6 to row 9.
I was of the understanding that the form code inserted a new row 7 to put the new data in. Is that correct?
If so that formula would keep row 6 as its starting row and would become =SUM(B6:B10)
So I'm not understanding how "the simple formula moves down to the next row as the starting point". Can you enlighten me so I can see what I am missing or mis-interpreting?

I'm also struggling to match this latest comment with the one in post 5 which said "all works well"?

.. and just to confirm: Are you saying that the formula in post 13 also does not do what you want?

 

[Pianostool]

So that I can understand that, can you spell it out a bit more for me?
=SUM(B6:B9)
(might not be B9 but whatever is the last row of that first section when the formula is first placed into C5)
This adds from row 6 to row 9.
I was of the understanding that the form code inserted a new row 7 to put the new data in. Is that correct?
If so that formula would keep row 6 as its starting row and would become =SUM(B6:B10)
So I'm not understanding how "the simple formula moves down to the next row as the starting point". Can you enlighten me so I can see what I am missing or mis-interpreting?

I'm also struggling to match this latest comment with the one in post 5 which said "all works well"?

.. and just to confirm: Are you saying that the formula in post 13 also does not do what you want?

Hi Peter, thanks for following through for clarification.
The first cell of the SUM formula is B7. When new data gets inserted via the form, it inserts a new row into row 7, so the simple formula then starts from B8 instead of remaining at B7.
Your longer formula from Post 13 does the same thing, doesn't include the new inserted row at the top.
And sorry for the confusion with "all works well" in Post 5. I was referring to changing the formula suggested by kvsrinivasamurthy to your suggested cell of B6, not your formula.
Anyway, all has worked now, even with the new addition of other data collection lines.
Thanks.

​