Ask Your Question
0

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

asked 2017-08-21 18:55:50 +0200

Danielsan gravatar image

updated 2017-08-21 18:56:41 +0200

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2017-08-21 19:51:47 +0200

JohnSUN gravatar image

updated 2017-08-21 19:54:31 +0200

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

edit flag offensive delete link more
0

answered 2017-08-21 20:30:37 +0200

Danielsan gravatar image

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!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-21 18:55:50 +0200

Seen: 315 times

Last updated: Aug 21 '17