Ho una macro filtro avanzata che filtra più colonne in un foglio di lavoro .
in questo foglio di lavoro, ha una formula nella cella "M1" che mi dà un risultato previsto quando la macro è in esecuzione, ma un ERRORE può verificarsi nella cella "M1" durante l'esecuzione della macro, cioè un errore di tipo #N/DISP .
quindi ho bisogno di Includere una Condizione nella macro, cioè se un ERRORE di tipo #N/DISP si verifica nella cella "M1" durante l'esecuzione della macro, allora la macro deve smettere di funzionare, cioè non dovrebbe continuare a funzionare, deve interrompere immediatamente la sua esecuzione .
di seguito è riportato il codice di filtro avanzato che sto utilizzando :
Codice: Seleziona tutto
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
sub Test_with_removeByName_warning
sub RetrieveTheActiveCell
abbracci amici