XL 2019 Macro pour supprimer des lignes et modifier des valeurs selon une correspondance

[Yoshi0302]

Bonjour à tous,

Merci pour votre aide pour toutes nos questions mais je suis face à un problème que je n'arrive pas à résoudre. Mes connaissances en macro sont trop limitées.
L'objectif de la macro est la suivante :

Macro qui supprime les lignes de la colonne E tout les comptes qui ne commencent pas par 2 et par 6

Macro qui récupère le 401XXX et qui supprime et remplace la valeur initiale en colonne R par le 401XXX en question ( sachant que la colonne F et K sont identiques avec le compte de charge, de TVA et du fournisseurs pour chaque transaction)

Macro qui remplace la valeur dans la colonne AE par la table de correspondance suivante : Internal ID Cpt TVA Logique 448​ 44566210​ Si AE est vide mais ligne du dessous : 44566210 alors 448 447​ 44562200​ Si AE est égal à 44562200: alors 447 446​ 44566000​ Si AE est égal à 44566000: alors 446 444​ 44566220​ Si AE est vide mais ligne du dessous : 44566220 alors 444 443​ 44566320​ Si AE est égale à 44566320 : alors 443 442​ 0​ Si AE est égal à 0 ( la cellule mentionne 0 et n'est donc pas vide): alors 442

Je sais que ma demande s'avère compliquée mais je suis perdu et je nais pas comment m'y prendre.
Merci pour votre aide.

 

[sylvanu]

Bonjour Yoshi,
J'ai supposé que les trois macros sont indépendants, puisque qu'après la première il n'y a plus que des comptes en 2 et 6.

Macro qui supprime les lignes de la colonne E tout les comptes qui ne commencent pas par 2 et par 6

Sub SuppLignesNon2et6()
    Application.ScreenUpdating = False
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Résultat
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ' Insertion colonne en A
    f = "=SI(ET(CNUM(GAUCHE(F2;1))<>2;CNUM(GAUCHE(F2;1))<>6);CAR(1);0)"           ' Formule utilisée
    With Range("A2:A" & DL)                             ' Plage où coller la formule qui sera triée, dernière colonne qui peut toujours être utilisée XFD
        .FormulaLocal = f                               ' Coller formule
        .EntireRow.Sort .Cells, xlDescending            ' Tri pour regrouper et accélérer
        .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete  ' Suppression des lignes concernées
    End With
    Columns("A:A").Delete Shift:=xlToLeft               'Suppression colonne A qui a été ajoutée
    Columns.AutoFit                                     'Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                'Ajustement barres de défilement
End Sub

Macro qui récupère le 401XXX et qui supprime et remplace la valeur initiale en colonne R par le 401XXX en question

Sub RemplaceCodes401xx()
    Application.ScreenUpdating = False
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Résultat
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ' Insertion colonne en A
    f = "=SI(CNUM(GAUCHE(F2;3))=401;F2;S2)"             ' Formule utilisée
    Range("A2:A" & DL).FormulaLocal = f                 ' Coller formule
    Range("S2:S" & DL) = Range("A2:A" & DL).Value       ' On compie les valeurs en col S ( soit R qd on aura supprimé la col A )
    Columns("A:A").Delete Shift:=xlToLeft               ' Suppression colonne A qui a été ajoutée
    Columns.AutoFit                                     ' Ajustement largeurs colonnes
    With ActiveSheet.UsedRange: End With                ' Ajustement barres de défilement
End Sub

Macro qui remplace la valeur dans la colonne AE par la table de correspondance suivante :

Sub RemplaceVATcode()
    Application.ScreenUpdating = False
    DL = [A65000].End(xlUp).Row                         ' Dernière ligne de Résultat
    tablo = Range("AE2:AE" & DL)                        ' On met la colonne AE dans un tablo pour aller plus vite
    For i = 1 To UBound(tablo) - 1                      ' Pour tout le tableau (-1 pour s'arreter à l'avant dernière ligne )
        If tablo(i + 1, 1) = 44566210 And tablo(i, 1) = "" Then tablo(i, 1) = 448   ' Traitement des différents cas.
        If tablo(i + 1, 1) = 44566220 And tablo(i, 1) = "" Then tablo(i, 1) = 444
        If tablo(i, 1) = 44562200 Then tablo(i, 1) = 447
        If tablo(i, 1) = 44566000 Then tablo(i, 1) = 446
        If tablo(i, 1) = 44566320 Then tablo(i, 1) = 443
        If tablo(i, 1) = 0 Then tablo(i, 1) = 442
    Next i
    [AE2].Resize(UBound(tablo, 1), UBound(tablo, 2)) = tablo    ' Restitution tableau
End Sub

 

[Yoshi0302]

Merci Sylvanu pour ton retour
Cependant j'ai un soucis avec la 1ère macro car elle ne rajoute pas le 401XXX sur les lignes qui commencent par un 6 en colonne E ( un zero est mis à la place )

Merci enormement pour ton aide

 

[sylvanu]

La première macro est celle ci :

Macro qui supprime les lignes de la colonne E tout les comptes qui ne commencent pas par 2 et par 6

Elle ne rajoute rien.

De plus pourquoi "sur les lignes qui commencent par un 6 en colonne E" ? Que fait on des lignes qui commencent par 2 ?

La seconde est celle ci :

Macro qui récupère le 401XXX et qui supprime et remplace la valeur initiale en colonne R par le 401XXX en question ( sachant que la colonne F et K sont identiques avec le compte de charge, de TVA et du fournisseurs pour chaque transaction)

C'est elle qui rajoute les 401xxx.

Si c'est pas çà, expliquez vous plus clairement, car mes macros font strictement ce que vous avez demandé.

 

[Yoshi0302]

La première macro est celle ci :

Elle ne rajoute rien.

De plus pourquoi "sur les lignes qui commencent par un 6 en colonne E" ? Que fait on des lignes qui commencent par 2 ?

La seconde est celle ci :

C'est elle qui rajoute les 401xxx.

Si c'est pas çà, expliquez vous plus clairement, car mes macros font strictement ce que vous avez demandé.

Merci pour votre retour
Concernant la macro RemplaceCodes401xx , je vous transmets un fichier excel avec
Onglet 1 : fichier avant macro
Onglet 2 : fichier après votre macro
Onglet 3 : resultat attendu

J'ai limité le fichier d'exemple juste aux colonnes concernées du fichier 'fichier test de valeurs'

Merci

 

[sylvanu]

Là, désolé, je n'aurais pas le temps.
D'autant que je ne comprends pas.
A voir votre PJ, dès que ça commence par 6 on prend le 1er en 401.
Mais dans ce cas, 61320090 c'est 401B mais pourquoi 60641100 c'est 401C, ça devrait être 401B, le premier trouvé. Sinon ça décale tout.
Si les deux premières macro, il vaut peut être mieux refaire un post que pour la seconde, en expliquant bien, car même avec votre fichier les règles semblent un peu floues.

 

[Yoshi0302]

En effet , j'ai fait une erreur dans le fichier mais en effet 60641100 c'est 401B
Pour résumer :
Chaque transaction est composée d'un compte 6 au minimum, un compte 44 ou non et un unique 401XXX
L'objectif est d'avoir ce 401XXX qui remplacerait les valeurs dans la colonne R
La finalité est d'avoir uniquement des comptes 6 avec en colonne R, le bon 401XXX

Merci de prendre du temps pour ma demande c'est vraiment tres gentil mais il n'y'a pas de probleme j'attendrais votre retour
Merci encore