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

[Captrquig]

.

 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

.

[Captrquig]

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

[newbie-02]

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.

[schiavinatto]

.

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 to the left of the answer, to finish the question.

[Captrquig]

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]

no help but thanks anyway