Autosum new entries

[CazorDK]

Hi,

I'm fairly new to the whole VBA world.

I have made a simpel button that adds a new row below a certain cell, adds a text to the new B# cell and a number to the new C# cell.

I also used the =SUM(INDIRECT("C18:C"&ROW()-1)) below to sum up the new numbers. So far so good.

Now I need another button and rows that does the same, but below the first sum, and thats a problem for me.

The problem is that I did the same thing further down the sheet, again using =SUM(INDIRECT("C26:C"&ROW()-1)). But if I pushed the first button say three times, three new rows are put into the sheet and the second autosum isn't working anymore since its no longer "C26" but now "C29" thats the start of the new sum.

If you look at the picture, what happens is when I press my button it inserts a new row and adds 10,000.00 after row 18, and the autosum keeps adding up perfectly - but every time I press the button the Liabilities is moved 1 row down and the formula isnt working.

Any tips?

[Richard Buttrey]

Hi and welcome to the forum.
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[CazorDK]

Hi - just uploaded the workbook.

Don't mind the button "Indtægter" and "Rådighedsbeløb" as well as cell A1:C16, they are not part of the problem.

The code is on "Knap 7" button and is

Sub Knap7_Klik()

ActiveSheet.Cells(18, 1).Select
Rows(19).Insert Shift:=xlShiftDown
ActiveCell.Offset(1, 0).Value = "ejendom"
ActiveCell.Offset(1, 1).Value = "tDKK"
ActiveCell.Offset(1, 2).Value = CDbl("10000")
ActiveCell.Offset(1, 2).NumberFormat = "#,###"

End Sub

The first autosum is - without data in cell C20 and works fine when button "Knap 7" is pushed - the problem is the autosum under Liabilties (without new rows cell C25).

[CazorDK]

Hi - just uploaded the workbook.

Don't mind the button "Indtægter" and "Rådighedsbeløb" as well as cell A1:C16, they are not part of the problem.

The code is on "Knap 7" button and is

Sub Knap7_Klik()

ActiveSheet.Cells(18, 1).Select
Rows(19).Insert Shift:=xlShiftDown
ActiveCell.Offset(1, 0).Value = "ejendom"
ActiveCell.Offset(1, 1).Value = "tDKK"
ActiveCell.Offset(1, 2).Value = CDbl("10000")
ActiveCell.Offset(1, 2).NumberFormat = "#,###"

End Sub

The first autosum is - without data in cell C20 and works fine when button "Knap 7" is pushed - the problem is the autosum under Liabilties (without new rows cell C25).

The button "knap 7" should just put a new row below row 18 with the phrase "ejendom" in A column, "tDKK" in B column and 10,000 in C column, and the autosum under Assets should just auto sum.

I want to build a button the same under Liabilities, but I cant figure out how to autosum, as it moves the rows down, so the autoformula wont work.