Const PremCelSource$ = "B4" '1ère cellule source, modifiable
Const PremCelResultat$ = "C2" '1ère cellule des résultats, modifiable
Const decale As Byte = 4 'décalage des résultats
Sub Traitement()
Dim Tags, ub%, S As Worksheet, R As Worksheet, nlig&, col%, mat, resu$(), i&, x$, j%, y$, deb%, fin%
Tags = Array("Conti CQTS ref.", "Vehicule", "Km", "Start of warranty", "Country", "Dealer Brand", "Cal") 'liste des tags à adapter
ub = UBound(Tags)
Set S = Sheets("Source"): Set R = Sheets("Résultat")
nlig = S.Cells(Rows.Count, S.Range(PremCelSource).Column).End(xlUp).Row - S.Range(PremCelSource).Row + 1
Application.ScreenUpdating = False
With R
col = .Range(PremCelResultat).Column
.Columns(col).Resize(, .Columns.Count - col + 1).Delete 'RAZ
If nlig < 1 Then Exit Sub
.Columns(col).ColumnWidth = 100
S.Range(PremCelSource).Resize(nlig).Copy .Range(PremCelResultat)
'---analyse des textes---
mat = .Range(PremCelResultat).Resize(nlig, 2) 'matrice, plus rapide, au moins 2 éléments
ReDim resu(1 To nlig, 0 To ub)
For i = 1 To UBound(mat)
x = mat(i, 1)
For j = 0 To ub
y = Tags(j)
deb = InStr(x, y)
If deb Then
deb = deb + Len(y)
x = x & vbLf 'bornage
fin = InStr(deb, x, vbLf)
resu(i, j) = Trim(Replace(Mid(x, deb, fin - deb), ":", ""))
End If
Next j, i
'---restitution des résultats
With .Range(PremCelResultat).Offset(, decale).Resize(nlig, ub + 1)
.Value = resu
If .Row > 1 Then .Rows(0) = Tags 'affichage des tags, facultatif ?
End With
.Columns(col).Resize(, .Columns.Count - col + 1).AutoFit 'ajustement largeur des colonnes
With .UsedRange: End With 'actualise les barres de défilement
.Activate
End With
End Sub