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
| Function ConcatVLookUp(ByVal ValRecherche, _
ByVal TabMatrice As Range, _
ByVal IndexCol, _
Optional ByVal blnConcat As Boolean = False, _
Optional ByVal Separateur = ";") As Variant
' Permet une recherchev sur des caractères génériques
'
Dim c As Range
Dim coll As Collection
Set coll = New Collection
Application.Volatile
For Each c In TabMatrice.Cells
If c.Value Like ValRecherche Then
On Error Resume Next
coll.Add c.Offset(0, IndexCol).Value, CStr(c.Offset(0, IndexCol).Value)
If Err.Number = 0 Then
ConcatVLookUp = ConcatVLookUp & Separateur & c.Offset(0, IndexCol).Value
End If
On Error GoTo 0
If Not blnConcat Then Exit For
End If
Next c
ConcatVLookUp = Mid(ConcatVLookUp, Len(Separateur) + 1)
Set c = Nothing
End Function |
Partager