8.ods (27.0 KB)
How can i create a macro so that it inserts 3 columns after each of those columns? It would take a lot of time to do it for each of them manually, i have more than 2 thousands columns. Excel has use relative references, calc doesn’t.
Thank you.
Maybe it is better to use the API functions of the LO in your macros:
Sub InsertColunms
oCurrentSheet = ThisComponent.CurrentController.ActiveSheet
oCursor = oCurrentSheet.CreateCursor()
oCursor.gotoEndOfUsedArea(false)
lColumns = oCursor.RangeAddress.EndColumn
For i = lColumns-1 to 1 step -1
oCurrentSheet.Columns.insertByIndex(i, 3)
Next i
End Sub
1 Like
REM ***** BASIC *****
Sub InsertCol
GoToAdd "$Sheet1.$B$1"
Dim Var1 As string
Var1 = ThisComponent.getCurrentSelection.Formula()
Do While Var1 <>""
Execute "InsertColumnsAfter"
Execute "InsertColumnsAfter"
Execute "InsertColumnsAfter"
Execute "GoRightToEndOfData"
Var1 = ThisComponent.getCurrentSelection.Formula()
Loop
GoToAdd "A1"
End Sub
Sub GoToAdd ( xAdd$ )
'' Suporta os seguintes comandos: ( xAdd$ )
'' Planilha ........... = "Plan1"
'' Planilha.Célula .... = "Plan1.C2"
'' Planilha.Área ...... = "Plan1.D5:V9"
'' Célula ............. = "C3"
'' Área ............... = "H6:K12"
'' Área Nomeada ....... = "AreaNomeada"
'' Linha .............. = "13"
'' Faixa de Linhas .... = "16:19"
'' Coluna ............. = "H:H"
'' Faixa de Colunas ... = "D:U"
dim Address(0) as new com.sun.star.beans.PropertyValue
Address(0).Name = "ToPoint" : Address(0).Value = xAdd
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame _
, ".uno:GoToCell", "", 0, Address())
End Sub
Sub Execute ( oQue$, Optional xValue% )
'' Os comando iniciados com Go___ , é obrigatório
'' o valor da variável xValue, se maior que 1 (um).
If IsMissing ( xValue ) Then : xValue = 1 : End If
Dim Goo(1) as new com.sun.star.beans.PropertyValue
Goo(0).Name = "By" : Goo(0).Value = xValue
Goo(1).Name = "Sel" : Goo(1).Value = false
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController _
.Frame, ".uno:" & oQue & "", "", 0, Goo())
End Sub