Macro ou formule pour concilier des montants

Bonjour

Merci encore pour votre support

Bonne journée

Ça va venir ! J'ai réadapté le tableau de sélection et le lancement. et écrit la procédure Garda...

Je vais tester, mais tu m'accorderas bien une petite pause... Cela devrait aller vite si je ne me suis pas mélangé les pieds...

@+

Ça a l'air de fonctionner sans erreur. A toi de vérifier que cela remplit bien les conditions...

J'ai donc ajouté une colonne au tableau de sélection, qui occupe la plage C4:H6.

La nouvelle plage a remplacé l'ancienne dans les 2 procédures associées aux boutons.

Et pour le bouton de lancement, une variable (proc) String ajoutée pour recueillir le nom de la procédure à lancer, laquelle n'est plus alors lancée directement mais par :

               Application.Run proc, fB, fR, dB, dR, nB, nR, ivB, ivR

Les procédures de traitement ont été adaptées :

Sub ConcilierLots(fB As String, fR As String, dkB As Integer, dkR As Integer, _
 nkB As Integer, nkR As Integer, invkB As Boolean, invkR As Boolean)
    Dim d As Object, RQE, RsA, k, clr, i&, plgBD As Range, ik1B%, ik2B%, ik1R%, ik2R%
    clr = Array(RGB(204, 255, 153), RGB(255, 51, 0))
    Set d = CreateObject("Scripting.Dictionary")
    Set plgBD = Worksheets(fB).Range("A1").CurrentRegion.Offset(, dkB).Resize(, nkB)
    RQE = plgBD.Value
    ik1B = IIf(invkB, nkB, 1): ik2B = IIf(invkB, 1, nkB)
    'Décoloration colonnes BD
    With plgBD.Offset(1)
        .Columns(ik1B).Interior.ColorIndex = xlColorIndexNone
        .Columns(ik2B).Interior.ColorIndex = xlColorIndexNone
    End With
    'Constitution dico Lots(BD)—›tableau(ligne,Mt)
    For i = 2 To UBound(RQE)
        If RQE(i, ik1B) <> "" Then d("Lot" & RQE(i, ik1B)) = Array(i, RQE(i, ik2B))
    Next i
    'Feuille Rapport
    With Worksheets(fR).Range("A1").CurrentRegion.Offset(, dkR).Resize(, nkR)
        RsA = .Value
        ik1R = IIf(invkR, nkR, 1): ik2R = IIf(invkR, 1, nkR)
        'Décoloration colonnes Rapport
        With .Offset(1)
            .Columns(ik1R).Interior.ColorIndex = xlColorIndexNone
            .Columns(ik2R).Interior.ColorIndex = xlColorIndexNone
        End With
        'Comparaison pour Mt<>0: correspondance Lot BD//Rap
        ' si pas de corresp. Lot: non trouvé...
        ' si corresp. coloration vert(si coincidence Mt), rouge(sinon)
        ' et suppr. élément dico si corresp. Lot
        For i = 2 To UBound(RsA)
            If RsA(i, ik2R) <> 0 Then
                k = "Lot" & RsA(i, ik1R)
                If d.exists(k) Then
                    RQE = d(k)
                    If CDec(RQE(1)) = RsA(i, ik2R) Then
                        .Cells(i, ik1R).Interior.Color = clr(0)
                        .Cells(i, ik2R).Interior.Color = clr(0)
                        plgBD.Cells(CLng(RQE(0)), ik1B).Interior.Color = clr(0)
                        plgBD.Cells(CLng(RQE(0)), ik2B).Interior.Color = clr(0)
                    Else
                        .Cells(i, ik1R).Interior.Color = clr(1)
                        .Cells(i, ik2R).Interior.Color = clr(1)
                        plgBD.Cells(CLng(RQE(0)), ik1B).Interior.Color = clr(1)
                        plgBD.Cells(CLng(RQE(0)), ik2B).Interior.Color = clr(1)
                    End If
                    d.Remove k
                End If
            End If
        Next i
    End With
End Sub

C'est l'ancienne procédure Concilier, dans laquelle les éléments qui étaient indiqués par leur valeur, le sont maintenant au moyen de variables.

Elle reçoit en arguments les noms des feuilles concernées par le traitement (BD et Rapport), et pour chaque feuille : le décalage de la colonne Lot par rapport à A, le nombre de colonnes incluant la colonne Lot et la colonne Montant (dans cet ordre ou inverse), et une valeur booléenne à Vrai si la colonne Montant précède la colonne Lot.

A partir de là, elle peut cibler les plages utiles (au traitement considéré) des feuilles, et définir dans des variables les colonnes respectivement Lot et Montant de ces plages (dont l'une sera la première colonne de la plage et l'autre la dernière).

J'ai balisé les différentes parties du traitement afin de voir ce qui ne varie pas et ce qui change entre cette procédure et la suivante (destinée à traiter le rapport Garda).

Sub ConcilierClients(fB As String, fR As String, dkB As Integer, dkR As Integer, _
 nkB As Integer, nkR As Integer, invkB As Boolean, invkR As Boolean)
    Dim d As Object, RQE, RsA, k, clr&, i&, plgBD As Range, ik1B%, ik2B%, ik1R%, ik2R%, j%
    clr = RGB(204, 255, 153)
    Set d = CreateObject("Scripting.Dictionary")
    'Feuille BD
    Set plgBD = Worksheets(fB).Range("A1").CurrentRegion.Offset(, dkB).Resize(, nkB)
    RQE = plgBD.Value
    ik1B = IIf(invkB, nkB, 1): ik2B = IIf(invkB, 1, nkB)
    'Décoloration colonnes BD
    With plgBD.Offset(1)
        .Columns(ik1B).Interior.ColorIndex = xlColorIndexNone
        .Columns(ik2B).Interior.ColorIndex = xlColorIndexNone
    End With
    'Constitution dico Mt(BD)—›chaîne(vide;ligne;cli[;ligne;cli...])
    On Error Resume Next
    For i = 2 To UBound(RQE)
        If RQE(i, ik2B) <> 0 Then
            k = "Mt" & RQE(i, ik2B)
            d(k) = d(k) & ";" & i & ";" & "Cli" & RQE(i, ik1B)
            If Err.Number <> 0 Then Err.Clear
        End If
    Next i
    On Error GoTo 0
    'Feuille Rapport
    With Worksheets(fR).Range("A1").CurrentRegion.Offset(, dkR).Resize(, nkR)
        RsA = .Value
        ik1R = IIf(invkR, nkR, 1): ik2R = IIf(invkR, 1, nkR)
        'Décoloration colonnes Rapport
        With .Offset(1)
            .Columns(ik1R).Interior.ColorIndex = xlColorIndexNone
            .Columns(ik2R).Interior.ColorIndex = xlColorIndexNone
        End With
        'Recherche correspondance Mt BD//Rap
        ' si corresp. recherche Client dans liste dico Mt(BD)
        ' si trouvé coloration vert (pas de rouge car client apparaît plusieurs fois)
        ' et effacement ligne et client de la liste de l'élément dico
        For i = 2 To UBound(RsA)
            k = "Mt" & RsA(i, ik2R)
            If d.exists(k) Then
                RQE = Split(d(k), ";")
                For j = 2 To UBound(RQE) Step 2
                    If RQE(j) = "Cli" & RsA(i, ik1R) Then
                        .Cells(i, ik1R).Interior.Color = clr
                        .Cells(i, ik2R).Interior.Color = clr
                        plgBD.Cells(CLng(RQE(j - 1)), ik1B).Interior.Color = clr
                        plgBD.Cells(CLng(RQE(j - 1)), ik2B).Interior.Color = clr
                        RQE(j) = "": RQE(j - 1) = "": d(k) = Join(RQE, ";")
                        Exit For
                    End If
                Next j
            End If
        Next i
    End With
End Sub

La définition des plages et des colonnes s'opère de la même façon, la colonne Client se substituant simplement à la colonne Lot de la proc. précédente. Les arguments passés sont semblables. Les variables également à deux détails près : on aura besoin d'un variable Integer supplémentaire dans le traitement, et la couleur est simplement définie dans une variable (dite scalaire, par opposition à un tableau, car la répétition d'un même client ne permet plus d'indiquer une non correspondance de montant pour un client comme on pouvait le faire par lot, on trouve une correpondance ou pas...)

Ce qui se différencie, c'est d'une part la constitution du dico : on constituait un dico Lot précédemment mais ici on ne constitue pas un dico Client (qui aurait été équivalent) mais un dico Montant, lesquels montants peuvent tout à fait se doublonner, tant pour un même client que pour des clients différents.

Pour une clé Montant donnée (en éliminant les montants à 0), on va donc en valeur de l'élément dico, lister le numéro de ligne et l'indication client. On aura donc une valeur composée de numéros de lignes et identifiants clients successifs, séparés par des ";" (débutant par un séparateur, on débute la liste par un élément vide).

Cette constitution est mise sous gestion d'erreur car l'indicatif client étant dans la Base renvoyée par une formule, s'il n'a pas été trouvé la formule renverra l'erreur #N/A, qui provoquerait une erreur.

D'autre part, lors du traitement du Rapport, sur chaque ligne on construit la clé Montant pour rechercher si un élément dico existe : s'il existe on récupère sa valeur en tableau (au moyen de Split) et on parcourt ce tableau à partir de l'indice 2, de 2 en 2, pour trouver une correspondance d'indicateur Client, si elle est trouvée, on colore les paires de cellules concernées dans les deux feuilles, et avant de passer à la ligne suivante on élimine les numéros de lignes et indicatif client du tableau, que l'on rétablit en chaîne (au moyen de Join) pour mettre à jour l'élément dico qui est ici conservé. Dans tous les autres cas, il n'y a pas eu de correspondance trouvée, on ne colore donc rien.

Cordialement.

Bonjour

C'est extraordinaire

Un gros merci je vais le tester , je vais te laisser savoir ...si totu fonctionne bien

Vous êtes vraiment un professionnel.

Bonne journée!