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