1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
| Function GetListWithCondition(TableName As String, _
LabelName As String, _
Operator As XlFormatConditionOperator, _
Value1 As Variant, _
Optional Value2 As Variant) As Variant
' Renvoie une table filtrée suivant critère
' Arguments
' Operator
' LabelName étiquette de colonne
' TableName nom de la table structurée
' Value1 Valeur à évaluer
' [Value2] 2ème valeur pour l'opérateur "entre" et "non compris entre"
Dim oTable As ListObject
Dim LookupRange As Range
Dim r As Long, c As Integer, nc As Integer, cr As Long
Dim t As Variant
Dim flag As Boolean
Set oTable = Range(TableName).ListObject
With oTable
Set LookupRange = .ListColumns(LabelName).DataBodyRange
nc = .Range.Columns.Count
End With
With LookupRange
For r = 1 To LookupRange.Count
Select Case Operator
Case xlBetween: flag = (.Cells(r).Value >= Value1 And .Cells(r).Value <= Value2)
Case xlEqual: flag = .Cells(r).Value = Value1
Case xlGreater: flag = .Cells(r).Value > Value1
Case xlGreaterEqual: flag = .Cells(r).Value >= Value1
Case xlLess: flag = .Cells(r).Value < Value1
Case xlLessEqual: flag = .Cells(r).Value <= Value1
Case xlNotBetween:
flag = (.Cells(r).Value < Value1 Or .Cells(r).Value > Value2)
Case xlNotEqual: flag = .Cells(r).Value <> Value1
End Select
' Si répond au critère
If flag Then
If cr Then
ReDim Preserve t(nc + 1, cr)
Else
ReDim t(nc + 1, cr)
End If
' Charge les données dans la table
For c = 0 To nc
t(0, cr) = r ' N° de la ligne répondant au critère
t(c + 1, cr) = oTable.DataBodyRange.Cells(r, c + 1).Value
Next
cr = cr + 1
End If
Next
End With
GetListWithCondition = Application.Transpose(t)
Set LookupRange = Nothing
End Function |
Partager