Can someone help me write a macro?

[istvnemeth1]

I’ve got a button, which can make me a new line, but I have to click somewhere to do it above.
So I need a change to make that new line above a cell with text “Total Sum”.

Here is my code right now:

sub New_Line
rem ----------------------------------------------------------------------
rem define variables 
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$9"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRowsBefore", "", 0, Array())

end sub

I think I need it to find the cell with that text and return the address of the cell, but I don’t understand this programming.

Thanks for every help.

[Lupp]

You shouldn’t use “macros” for everyday actions. Why do you think you need one?

Keeping and completing lists and calculating totals for some columns is absolute standard.

What may create a problem for you is the idea a total needs to be below the items. Put it above and freeze one or a few rows at the top of the sheet. Then append new data rows at the end of the already filled range. You get there easily with Ctrl+Arrowdown.
Much better!

[istvnemeth1]

Yes that is true, but I just realized during the day, when I had to add a line I also had to change the code under the auto filtered area. There is the “Total sum” line. So i could make another code continuously, to add up the line numbers in two cells.
Or if there any filter for this, that would be better

[Lupp]

@istvnemeth1

…I also had to change the code under the auto filtered area…

Sorry I don’t understand. What do you mean by “code under the auto filtered area”?
Are there cells with formulas? For what purpose do you use the AutoFilter? Did whatever you intended work as expected if you inserted a new row manually (via the UI)?
I would need an example file -and explanations- to understand better.

[EarnestAl]

If you want to subtotal a filtered range then you should use the Subtotal function

[Lupp]

I would (generally) dissuade from filter-sensitive functions. I’m suspecting them to introduce results easily misinterpreted by users not fully aware of the situation.
Pivot tables (DataPilot formerly) may help in some cases.
(Since I decided long ago to not use SUBTOTAL(), I’m lacking sufficient experience, probably.)