Calc - How converting a recorded Macro in a relative Macro?

[Danielsan]

Hi there,

this is my first post.

I have a large document where I have to check manually some duplicated rows and then applying some repetitive tasks, I tried to record the macro but it works only in the range where I registered the macro but I would like to use it wherever I need, so I guess I have to change the absolute value in relative. I tried to change the value with the offset command but I get an invalid range error, I didn’t understand what I did wrong and I am stuck now.

This is what I am trying to do: https://drive.google.com/file/d/0B8-C4aQv_slnYk1URVpEeFhDNW8/view

Basically is copy a value from a cell, paste it in the cell above, move in the column F of the line below again and digit z.

This is the macro I recorded:

sub Copy_and_Paste
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 = "$D$2"

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

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$D$1"

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

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

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$F$2"

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

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "StringName"
args6(0).Value = "z"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args6())


end sub

I have to change the “args” variables in something relative, but this formula doesn’t work from args1(0).Value = “$D$2” to args2(0).Value = “OFFSET(D; -1;)”, any suggestions on how converting the macro in a relative one?

Thanks,

Daniel

[JohnSUN]

Daniel, your task (since you described it) is solved by a much shorter code

Sub CopyValueAndTypeZ
Dim oCurrentSelection As Variant
Dim aCellAddress As New com.sun.star.table.CellAddress
Dim nColumn As Long, nRow As Long
Dim oSpreadsheet As Variant

	oCurrentSelection = ThisComponent.getCurrentSelection()
	aCellAddress = oCurrentSelection.getCellAddress()
	nColumn = aCellAddress.Column
	If nColumn = 3 Then ' Is it a column D?
		nRow = aCellAddress.Row
		If nRow > 0 Then ' Isn't current row first?
			oSpreadsheet = oCurrentSelection.getSpreadsheet()
			oSpreadsheet.getCellByPosition(3, nRow-1).setFormula(oCurrentSelection.getFormula())
Rem ...and set "z" to column F
			oSpreadsheet.getCellByPosition(5, nRow).setString("z")
		EndIf 
	EndIf 
End Sub

Just select any cell in column D (row number 2 or more) and run macro

[Danielsan]

Ehi man!!! let me scream: THAT WAS AMAZING!!!

It works super well, I am lesser than a newbie so I have been hitting my head on the wall until now!