tengo una macro de filtro avanzado que filtra varias columnas en una hoja de cálculo .
En esta hoja de cálculo, tiene una fórmula en la celda "M1" que me da un resultado esperado, pero un ERROR puede ocurrir en la celda "M1" durante la ejecución de la macro, es decir, un error de tipo: #N/DISP
es necesario incluir en la macro una Condición que si se produce un ERROR de tipo #N/DISP en la celda "M1" durante la ejecución, entonces la macro debe dejar de ejecutarse, es decir, no debe continuar ejecutándose, debe Detener inmediatamente su ejecución .
abajo sigue el código de filtro avanzado que estoy usando:
Código: Seleccionar todo
Sub Prog_MS
Dim searchString As String
Dim sheet As Object
dim oCell as object
sheet = ThisComponent.Sheets.getByName("4.1Tinto MS")
oCell = sheet.getCellRangeByName("M1")
ThisComponent.CurrentController.select(oCell)
Dim range As Object
range = sheet.GetCellRangeByName("A6:BA3000")
Dim filterDescriptor As Object
filterDescriptor = range.CreateFilterDescriptor(true)
Dim filterFields(11) As New com.sun.star.sheet.TableFilterField2
range.filter(filterDescriptor)
with filterDescriptor
.ContainsHeader = true
.UseRegularExpressions = true
end with
With filterFields(0)
.field = 3
.IsNumeric = false
.stringValue = "A0174"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
End With
With filterFields(1)
.field = 3
.IsNumeric = false
.stringValue = "A0168"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
.Connection = com.sun.star.sheet.FilterConnection.AND
End With
With filterFields(2)
.Connection = com.sun.star.sheet.FilterConnection.AND
.field = 4
.IsNumeric = false
.StringValue = "000001"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
End With
With filterFields(3)
.field = 4
.IsNumeric = false
.StringValue = "000001"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
.Connection = com.sun.star.sheet.FilterConnection.AND
End With
with filterFields(4)
.Connection = com.sun.star.sheet.FilterConnection.AND
.field = 5
.IsNumeric = false
.StringValue = "^A0033.*"
.Operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
end with
With filterFields(5)
.Field = 5
.IsNumeric = false
.StringValue = "^A9.*"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
.Connection = com.sun.star.sheet.FilterConnection.AND
End With
with filterFields(6)
.field = 5
.IsNumeric = false
.StringValue = "^A2005.*"
.Operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
.Connection = com.sun.star.sheet.FilterConnection.AND
end with
With filterFields(7)
.Connection = com.sun.star.sheet.FilterConnection.AND
.Field = 6
.IsNumeric = false
.StringValue = ".*" & "ELAS" & ".*"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
End With
With filterFields(8)
.Field = 6
.IsNumeric = false
.StringValue = ".*" & "ROMAN" & ".*"
.operator = com.sun.star.sheet.FilterOperator2.NOT_EQUAL
.Connection = com.sun.star.sheet.FilterConnection.AND
End With
with filterFields(9)
.Connection = com.sun.star.sheet.FilterConnection.AND
.field = 10
.IsNumeric = false
.StringValue = "v"
.Operator = com.sun.star.sheet.FilterOperator2.EQUAL
.Connection = com.sun.star.sheet.FilterConnection.AND
end with
with filterFields(10)
.Connection = com.sun.star.sheet.FilterConnection.AND
.field = 8
.IsNumeric = false
.StringValue = "^m.*"
.Operator = com.sun.star.sheet.FilterOperator2.EQUAL
end with
With filterFields(11)
.Field = 8
.IsNumeric = false
.StringValue = "^z.*"
.operator = com.sun.star.sheet.FilterOperator2.EQUAL
.Connection = com.sun.star.sheet.FilterConnection.OR
End With
With filterDescriptor
.filterFields2 = filterFields
.containsHeader = True
.isCaseSensitive = false
.useRegularExpressions = True
End With
range.Filter filterDescriptor
End Sub
pero no estoy seguro de que este tema del otro foro puede ayudar a resolver el problema ?
abrazo amigos .