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
| Option Explicit
Dim idc As Long ' index caractère
Dim pda As Integer ' position de l'accent
Dim pdc As Integer ' position du caractère
Dim acc As String ' accents
Dim cac As String ' caractères à accentuer
Dim chs As String ' chaine sélections
Dim chx As String ' choix sélection
Dim tbc As String ' table recherche
Dim tba ' table accents
Private Sub Worksheet_Change(ByVal sel As Range)
If Not Intersect(sel, [Choix]) Is Nothing Then
If Len([Choix].Value) > 2 Then
tba = Application.Transpose([accents].Cells.Value): cac = ""
For idc = 1 To UBound(tba): cac = cac & Left(tba(idc), 1): Next idc
acc = "_" & Join(tba, "_") & "_"
chx = "*" & [Choix].Value & "*": tbc = chx: chs = ""
Call ins_acc
Cells([resu].Row + 1, [resu].Column).Resize(Cells(Rows.Count, [resu].Column).End(xlUp).Row, 3).ClearContents
With Cells([Choisir].Row + 1, [Choisir].Column)
.Resize([Choisir].Count, 1).ClearContents: tba = Split(tbc, "|")
.Resize(UBound(tba), 1) = Application.Transpose(tba)
End With
[Base].AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[Choisir], CopyToRange:=[resu], Unique:=False
[E7].Activate: sel.Activate
End If
End If
End Sub
Public Sub ins_acc()
Dim idx As Long
Dim chc As String
For idc = 1 To Len(chx)
pdc = InStr(cac, Mid(chx, idc, 1)): If pdc > 0 Then Exit For
Next idc
If idc <= Len(chx) Then
pda = InStr(acc, "_" & Mid(chx, idc, 1))
For idx = pda + 2 To Len(acc)
chc = Replace(chx, Mid(cac, pdc, 1), Mid(acc, idx, 1))
tbc = tbc & "|" & chc: chs = chs & chc & "|"
If Mid(acc, idx + 1, 1) = "_" Then Exit For
Next idx
idx = InStr(chs, "|"): chx = Left(chs, idx - 1): chs = Mid(chs, idx + 1): Call ins_acc
End If
End Sub |
Partager