Ask Your Question
0

Is there a way to create a macro that works relative to the row it is on ?

asked 2018-09-07 13:04:44 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I would like to do the following:

Create a macro button that merges cells from Column A1 to T1 then move to the next row (Row2) If i click it again it merges A2 to T2 and move to the next row. Then row 3, 4 , 5 etc.

So every time the button is pressed it merges A1 through T1 and goes to the next row.

What happens when I try this is that it keeps going back to Row A1. It doesn't seem to advance relative to where the line is at. I would like the macro to merge and advance from any line I chose starting at column A(x) and merging to T(x).

The Macro is as follows:

==================================================================================
REM  *****  BASIC  *****

sub CenterMacro
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 = "$A$383"

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$383:$T$383"

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

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

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

end sub
edit retag flag offensive close merge delete

Comments

Why? Tell me why you think that this question must be marked as wiki-post?!!

JohnSUN gravatar imageJohnSUN ( 2018-09-07 13:37:37 +0200 )edit

Show the Macro you are using.

peterwt gravatar imagepeterwt ( 2018-09-07 15:18:55 +0200 )edit

REM BASIC

Sub Main

End Sub

sub CenterMacro 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 ---------------------------------------------------------------

jcf304 gravatar imagejcf304 ( 2018-09-07 21:09:51 +0200 )edit

The Macro you posted is obviously not complete. Edit your original question to show the complete Macro.

peterwt gravatar imagepeterwt ( 2018-09-07 23:16:01 +0200 )edit

THe complete macro is now inlcuded

jcf304 gravatar imagejcf304 ( 2018-09-08 04:14:58 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-09-08 09:58:43 +0200

JohnSUN gravatar image
Sub CenterMacro
Dim oSheet As Variant, oCellRange As Variant, oCell As Variant
Dim aCellAddress As New com.sun.star.table.CellAddress

    oCell = ThisComponent.getCurrentSelection().getCellByPosition(0, 0)
    aCellAddress = oCell.getCellAddress()
    oSheet = oCell.getSpreadsheet()
    oCellRange = oSheet.getCellRangeByPosition(0, aCellAddress.Row, 19, aCellAddress.Row)

    If Not oCellRange.getIsMerged() Then 
        oCellRange.merge(True)
        oCellRange.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
    EndIf 
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-09-07 13:04:44 +0200

Seen: 49 times

Last updated: Sep 08 '18