Sub suppvirgule()
Dim x As Range, y As Range, b As Boolean
For Each x In Range("F2:F" & Range("F65536").End(xlUp).Row)
b = False
For Each y In Range("G4:G" & Range("G65536").End(xlUp).Row)
If x.Value = y.Value Then b = True
Next y
If b = False Then x.Replace What:=",*", Replacement:=" "
Next x
End Sub
Sub suppvirgule()
Dim Trouve As Range, FirstAddress As String, AncLig As String
'on cherche en colonne F la première virgule
With Columns("F:F")
Set Trouve = .Find(",", LookIn:=xlValues, LookAt:=xlPart)
If Not Trouve Is Nothing Then
'on identifie la première adresse
FirstAddress = Trouve.Address
Do
'si la cellule actuelle ne se trouve pas dans la liste d'exception
If Range("G4", [G65536].End(xlUp)).Find(Trouve.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
Trouve.Value = Replace(Trouve.Value, ",", " ")
End If
'on garde la ligne de la cellule actuelle
AncLig = Trouve.Row
Set Trouve = .Find(",", after:=Trouve, LookIn:=xlValues, LookAt:=xlPart)
'on boucle tant que l'adresse de la nouvelle est différente de la première
'ET tant que la recherche suivante ce fait vers le bas (pour éviter de boucler sans fin sur les exceptions)
Loop While Not Trouve Is Nothing And Trouve.Address <> FirstAddress And Trouve.Row > AncLig
End If
End With
End Sub
Sub suppvirgule2()
Dim t1() As Variant, t2() As Variant, i As Integer, j As Integer, b As Boolean
t1 = Range("F2:F" & Range("F65536").End(xlUp).Row).Value
t2 = Range("G4:G" & Range("G65536").End(xlUp).Row).Value
For i = LBound(t1, 1) To UBound(t1, 1)
b = False
For j = LBound(t2, 1) To UBound(t2, 1)
If t1(i, 1) = t2(j, 1) Then b = True
Next j
If b = False Then t1(i, 1) = Replace(t1(i, 1), ",", " ")
Next i
Range("F2:F" & UBound(t1, 1) + 1).Value = t1
End Sub