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

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

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

edit retag close merge delete

Sort by » oldest newest most voted

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

Sub CopyValueAndTypeZ
Dim oCurrentSelection As Variant
Dim nColumn As Long, nRow As Long

oCurrentSelection = ThisComponent.getCurrentSelection()
If nColumn = 3 Then ' Is it a column D?
If nRow > 0 Then ' Isn't current row first?
Rem ...and set "z" to column F
EndIf
EndIf
End Sub


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

more

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!

more