XL 2019 Fonction SI+Caractère spéciaux

Sub dupliquer_étiquette_perso_2()


    Dim wk_file As Workbook
    Dim ws_data As Worksheet
    Dim ws_export As Worksheet
    Dim lstrw As Long
    Dim rw_copy As Long
    Dim quantite As Long
    Dim statut As String

    'effacer contenu
    Sheets(2).Select
    Range("A2:H50000").ClearContents
    'identifier le fichiers et les onglets
    Set wk_file = ActiveWorkbook
    Set ws_data = wk_file.Worksheets(1)
    Set ws_export = wk_file.Worksheets(2)
    'identifier la dernière ligne de nos données
    lstrw = ws_data.Cells(Rows.Count, 1).End(xlUp).Row
    
    'identifier statut
    statut = "préparation à faire"
    
    'boucle sur les données
    For i = 6 To lstrw
        quantite = ws_data.Cells(i, 4)
        strMateriel = ws_data.Cells(i, 3)
        statut = ws_data.Cells(i, 2)
        
        If Not (statut = "Prise en charge par PS" Or statut = "Préparation faite") And _
        Not (strMateriel = "1000 L Vert spécial verres") And _
        Not (strMateriel = "Caisses palettes pleines") And _
        Not (strMateriel = "Caisses palettes Ajourées") And _
        Not (strMateriel = "Caisses palettes Ajourées neuves") And _
        Not (strMateriel = "Caisses palettes Gueltas pleines") And _
        Not (strMateriel = "120 L Anthracite") And _
        Not (strMateriel = "240 L Anthracite") And _
        Not (strMateriel = "360 L Anthracite") And _
        Not (strMateriel = "660 L Anthracite") And _
        Not (strMateriel = "770 L Anthracite") And _
        Not (strMateriel = "1100 L Anthracite") And _
        Not (strMateriel = "1000 L Jaune") And _
        Not (strMateriel = "Cartons de saches 400 L ""Décathlon""" And quantite > 40) And _
        Not (strMateriel = "Cartons de saches 1000 L" And quantite > 40) And _
        Not (strMateriel = "Supports saches 1000 L" And quantite > 40) And _
        Not (strMateriel = "Supports saches 200 L" And quantite > 40) And _
        Not (strMateriel = "Supports saches 400 L" And quantite > 40) And _
        Not (strMateriel = "Cartons de saches 200 L " And quantite > 40) Then
                'boucle sur la quantité
                For k = 1 To quantite
                    'identifier la ligne de collage
                    rw_copy = ws_export.Cells(Rows.Count, 1).End(xlUp).Row + 1
                    'coller les infos
                    With ws_export
                        .Cells(rw_copy, 1) = ws_data.Cells(i, 1)
                        .Cells(rw_copy, 2) = ws_data.Cells(i, 2)
                        .Cells(rw_copy, 3) = ws_data.Cells(i, 3)
                        .Cells(rw_copy, 4) = ws_data.Cells(i, 4)
                        .Cells(rw_copy, 5) = ws_data.Cells(i, 5)
                        .Cells(rw_copy, 6) = ws_data.Cells(i, 6)
                        .Cells(rw_copy, 7) = ws_data.Cells(i, 7)
                        .Cells(rw_copy, 8) = ws_data.Cells(i, 8)
                    End With
                Next
        End If
    Next
    MsgBox "Traitement de la donnée effectuée"
End Sub