Help needed for Calc macro

[ROSt53]

I am trying to rearrange data in Calc to suit the needs for further processing in Calc.

My understanding of Calc and macros tells me it should be possible to create such a macro. However my knowledge of writing Calc macros is too little to challenge such a macro. With this post, a cry for help, I hope that one of the Calc macro freaks around here will help me with the needed macro.

The macro needs to identify certain cells and then move the cells into a different position, add 5 neighboring cells and delete 5 neighboring cells.

This needs to be done several times until the end to the data range. The number of rows between the cells, which needs to moved, are at random.

In the attached Calc file all is made visible.

Columns A, B, C, D, E, F contain the data as they are taken from a pdf-File and pasted into Calc in position A1.

Columns H, I, J, K. L contain the data as they need to arranged by the macro.

In the columns B, C, D (original data) there are yellow and green marked cells. These cells need to be moved into the position shown in columns I, J. K, L.

The dark blue cells need to added that below cells are moved down.

The orange marked cells need to deleted that the below cells move up.

It can be seen that with this macro the original data arrangement will be made into a consistentFileForHelpOnMacro_LibOSupport.ods pattern from the top of the data range until its end.

I think it will not matter, but I am using LibO 5.4 on Linux Mint 17.3 KDE (still my workhorse; both versions are ancient, I know) but the new PC is under installation with Linux Mint 19.3 Cinnamon and the latest LibO version will be installed. Thus, if needed LibO 6.x can be used.

I do appreciate your help in this matter very much.

[Lupp]

Hello, old friend,

You were a bit scarce in this place the last few years. Hope you are well!

You may want to try the attached example. It wasn’t pure fun to write that rough code because it was obvious that it wouldn’t be usable in connection with a second task. Very special, very strange. I cannot imagine how you want to get something reasonable this way. Well, such is life sometimes.

Your colours didn’t tell everything. I concluded, the hot-spots would be where two subsequent cells in column B have content … If I was wrong, you will hopefully find a way to adapt the code to your actual needs.

ask248626complicatedRearrangement_1.ods

[ROSt53]

Hi old friend,

First, thanks for remembering me. To be honest, I miss working here. Meeting people like you and a good bunch of other well known supporters was a great time. I still dream about coming back. Let me see how my situation develops in August and thereafter. By then I also should be familiar with the v6.x.

If there is way to contact me outside the forum, please do so; it would be nice. I might have a nice surprise for you.

Second, thanks for the macro, I will have a look tomorrow my morning (UTC+9) and get back to you.

[Lupp]

I don’t know a way to contact you by PM / mail, but you will find one to contact me.
By the way: I recently reported tdf#133459 . Do you also have a login to the bug-tracker?
A slightly reworked version of the above attached example you find here.

[ROSt53]

I had a look at the macro with my extremely limited macro knowledge and consider the following part as the one which defines the working range.

With aRangeAddress    
  .Sheet = sourceRange.RangeAddress.Sheet    
  .StartColumn = keyColumn.RangeAddress.StartColumn    
  .EndColumn   = keyColumn.RangeAddress.StartColumn + pNumNames - 1    
  aTargetAddress.Sheet = .Sheet    
  aTargetAddress.Column  = .StartColumn + 2

Continued in the next comment

[ROSt53]

*The following part seems to be the one, which moves the cells into the right place and inserts 3 cells by moving cells down.
What is missing is deleting 4 horizontal cells, 2 rows below the row into which the 4 horizontal cells were inserted.

  For i = highestRow - 1 To 0 Step - 1
    If (keyDA(i)(0)<>"") AND (keyDA(i+1)(0)<>"") Then
      .StartRow = i + 1
      .EndRow   = i + 1
      namesRg = pSheet.getCellRangeByPosition(.StartColumn, .StartRow, .EndColumn, .EndRow) 
      aTargetAddress.Row = i
      pSheet.copyRange(aTargetAddress, aRangeAddress)
      namesRg.clearContents(959)
      .StartRow = .StartRow - 1
      .EndRow   = .EndRow - 1
      pSheet.insertCells(aRangeAddress, 3)
    End If
  Next i
End With
delColumn = sourceRange.Columns(pRelKeyColumn + 1)
pSheet.removeRange(delColumn.RangeAddress, 4)
End Sub

If I could get help on this last element, I am very happy person and my friend even more.

[ROSt53]

STOP - Specifications have changed, I will create a new post and hope I can get some help.

[schiavinatto]

Hello @ ROSt52, the macro does not find the cell in yellow, it is necessary to select the yellow cell and activate the macro, in the button above, one by one.

REM  *****  BASIC  *****

sub Mover
	Execute "GoDown"
	Execute "GoRightToEndOfDataSel"
	Execute "Cut"
	Execute "GoUp"
	Execute "GoRight"
	Execute "Paste"
	Execute "InsertRowsBefore"
	Execute "GoDown"
	Execute "GoDown"
	Execute "DeleteRows"
End Sub

Sub Execute ( oQue$ )
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:" & oQue & "", "", 0, Array())
End Sub

[ROSt53]

Thanks also for your macro!. I will have a look in the next days and get back to you as well.