Ask Your Question
0

advanced filter used in a macro that inserts to a new location places formula instead of value. is there a way to insert value?

asked 2020-03-26 00:43:33 +0100

Captrquig gravatar image

updated 2020-08-18 13:22:14 +0100

Alex Kemp gravatar image

.

 Sub findname
  Dim oSheet     'A sheet from the Calc document.
  Dim oRanges    'The NamedRanges property.
  Dim oCritRange 'Range that contains the filter criteria.
  Dim oDataRange 'Range that contains the data to filter.
  Dim oFiltDesc  'Filter descriptor.

  REM Range that contains the filter criteria
  oSheet = ThisComponent.getSheets().getByIndex(2)
rem   oCritRange = osheet.UseRegularExpressions() 'expression not found
  oCritRange = oSheet.getCellRangeByName("aa9:aa10")

  REM You can also obtain the range containing the 
  REM filter criteria from a named range.
  REM oRanges = ThisComponent.NamedRanges
  REM oRange = oRanges.getByName("AverageLess80")
  REM oCritRange = oRange.getReferredCells()

  REM The data that I want to filter
  oSheet = ThisComponent.getSheets().getByIndex(2)
  oDataRange = oSheet.getCellRangeByName("a11:p28")

  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)

rem  oDataRange.filter(oFiltDesc)
 REM Copy the output data rather than filter in place.  
  oFiltDesc.CopyOutputData = True

  REM Create a CellAddress and set it for Sheet3,
  REM Column B, Row 4 (remember, start counting with 0)
  Dim x As New com.sun.star.table.CellAddress
  x.Sheet = 16
  x.Column = 0
  x.Row = 0

  oFiltDesc.OutputPosition = x
   oDataRange.filter(oFiltDesc)
End Sub

.

edit retag flag offensive close merge delete

Comments

sorry about the macro alignment not shur how to do that I just copied and pasted

Captrquig gravatar imageCaptrquig ( 2020-03-26 00:49:03 +0100 )edit

would it be possible to reach your goal by using formulas with (partly) absolute references?

'=$sheet1.$A$1' instead of '=sheet1.A1'?

those should result in the same value after being displaced by the filter.

newbie-02 gravatar imagenewbie-02 ( 2020-05-24 12:23:37 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-03-26 03:01:29 +0100

.

Sub FiltroAvancado
'O############################################################################O
oSpreadsheet = "Planilha1"
oRange = "A1:D5"
oFilter = "F1:I2"
oExit = "A11"
Dim oDoc, oPlanAtiva, oPlan2, oIntervalo, oCriterios, oDestino, oFiltro As Object
oIntervalo = ThisComponent.Sheets.getByName( oSpreadsheet ).getCellRangeByName( oRange )
oCriterios = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName( oFilter )
oDestino = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName( oExit )
oFiltro = oCriterios.createFilterDescriptorByObject( oIntervalo )
oFiltro.CopyOutputData = True
oFiltro.OutputPosition = oDestino.CellAddress
oFiltro.ContainsHeader = True
oIntervalo.Filter( oFiltro ) 'Filtrar
'-----------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue : args1(0).Name = "ToPoint" : args1(0).Value = oExit
CreateUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args1())
CreateUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoRightToEndOfDataSel", "", 0, Array())
CreateUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoDownToEndOfDataSel", "", 0, Array())
CreateUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Copy", "", 0, Array())
dim args5(0) as new com.sun.star.beans.PropertyValue : args5(0).Name = "ToPoint" : args5(0).Value = oExit
CreateUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args5())
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = "Flags" : args6(0).Value = "SVD" : args6(1).Name = "FormulaCommand" : args6(1).Value = 0
args6(2).Name = "SkipEmptyCells" : args6(2).Value = false : args6(3).Name = "Transpose" : args6(3).Value = false
args6(4).Name = "AsLink" : args6(4).Value = false : args6(5).Name = "MoveMode" :args6(5).Value = 4
CreateUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(ThisComponent.CurrentController.Frame, ".uno:InsertContents", "", 0, args6())
end Sub

.

test file

-----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

Comments

Yes my macro works like yours. The problem is if you place a formula in each cell in one column then it filters and shows the formula and results are incorrect in the new location i would like the value to be filtered and shown in the new location

Captrquig gravatar imageCaptrquig ( 2020-03-26 03:45:04 +0100 )edit

no help but thanks anyway

Captrquig gravatar imageCaptrquig ( 2020-03-30 12:05:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-26 00:43:33 +0100

Seen: 47 times

Last updated: Mar 26 '20