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

[jcf304]

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

[JohnSUN]

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

[peterwt]

Show the Macro you are using.

[jcf304]

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

[peterwt]

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

[jcf304]

THe complete macro is now inlcuded

[JohnSUN]

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