Ask Your Question

Calc - How converting a recorded Macro in a relative Macro? [closed]

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

Danielsan gravatar image

updated 2020-07-21 01:27:50 +0200

Alex Kemp gravatar image

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:

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("")

rem ----------------------------------------------------------------------
dim args1(0) as new
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
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
args5(0).Name = "ToPoint"
args5(0).Value = "$F$2"

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

rem ----------------------------------------------------------------------
dim args6(0) as new
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?



edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-21 01:27:58.578854

2 Answers

Sort by » oldest newest most voted

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
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")
End Sub

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

edit flag offensive delete link more

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

Question Tools

1 follower


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

Seen: 535 times

Last updated: Aug 21 '17