Ordre Alpha

Private Sub Worksheet_Change(ByVal target As Range)
    If target.Column = 3 And target.Row > 2 Then 'désignation change
        If isNewDesignation(target.Value) Then
            With Range("J2").End(xlDown).Offset(1)
                .Value = target.Value
                .Offset(0, 1).FormulaLocal = "=SOMMEPROD(($C$3:$C$236=""" & target.Value & """)*($D$3:$D$236))"
            End With
        End If
    End If
End Sub
 
Private Function isNewDesignation(target As String) As Boolean
 
    isNewDesignation = True
    Dim li As Long
    For li = 3 To Range("J2").End(xlDown).Row
        If Cells(li, 10).Value = target Then
            isNewDesignation = False
            Exit For
        End If
    Next li
End Function