Discussion :

[Alex020181]

Bonjour,

Juste une chose, il est possible aussi de remplacer la date comptable par la date GL.

Cordialement,

Bien sûr.
Voilà.

[Pierre Fauconnier]

[...]Mais il s'avère que dans la direction ou je travail, ils veulent que du vba.[...]

Jamais très bien compris pourquoi on impose une obligation de moyens plutôt que de résultat. Lorsqu'un comptable veut isoler les mouvements non lettrés, l'important me semble-t-il est qu'il obtienne les mouvements non lettrés et, a priori, de la manière la plus simple possible pour le non-informaticien qu'il est. C'est justement pour cela que Power Query a vu le jour: Libérer les non-informaticiens que sont les utilisateurs d'Excel de la charge de pondre du code... Vu tes demandes d'ajustement, tu n'as pas l'air de comprendre grand chose au code qui t'est donné, sinon tu pourrais l'ajuster toi-même à tes besoins réels. Ca veut dire que tu vas être dépendant de solutions codées que tu maîtrises mal au lieu de viser l'autonomie. Mais bon, soit.

Je me place dans l'optique que ce n'est pas un "one shot" car j'ai démontré que dans ce cas, Excel est nativement outillé pour réaliser manuellement le travail en 2 minutes. Dans le cas où l'on doit répéter l'opération avec des extractions régulières en provenance d'autres sources, alors je préconise de réfléchir "en Excel" de manière à être le moins dépendant possible d'un code VBA incompréhensible pour le non-initié et de:

1. mettre en place un classeur de travail avec les tableaux qui vont bien, déjà préparés et formulés. J'ai pour principe de ne jamais travailler sur l'extraction mais d'importer les données dans un classeur. Ca évite notamment de devoir transférer ton code de fichier d'extraction en fichier d'extraction. Les tableaux structurés créés dans ton fichier de travail vont grandement aider l'écriture du code;
2. Y importer les données par vba, en considérant que l'extraction a toujours la même forme (début à la même ligne et même colonne, soit B18), et feuille nommée toujours de la même façon (ou toujours unique). Ces prérequis sont à adapter à ta situation;
3. Programmer "en VBA pour Excel", c'est-à-dire faire en VBA ce qu'on aurait fait en Excel (copie de la colonne Description2, suppression des doublons, formule de solde non nul);
4. Ecrire un code qui "colle" à ce qui serait réalisé en Excel de manière à être autonome par la suite pour les ajustements et améliorations.

En envisageant la chose de cette façon, j'ai découpé le code VBA selon la finalité de chaque partie (préparation du classeur de travail, récupération des données, traitement des mouvements) de manière à coller aux méthodes Excel et à isoler chaque partie pour pouvoir la tester isolément du reste. C'est la procédure "Process" qui est lancée pour réaliser le traitement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
Option Explicit
 
Sub Process()
  Application.ScreenUpdating = False
 
  PrepareWorkbook
  GetData Range("t_Mouvements")
  PrepareData
 
  Application.ScreenUpdating = True
End Sub
 
Function PrepareWorkbook()
  Dim tMouvements As ListObject
  Dim tDescriptions As ListObject
 
  Set tMouvements = Range("t_Mouvements").ListObject
  tMouvements.AutoFilter.ShowAllData
  If Not tMouvements.DataBodyRange Is Nothing Then tMouvements.DataBodyRange.Delete
  tMouvements.ListRows.Add
 
  Set tDescriptions = Range("t_Descriptions2").ListObject
  tDescriptions.AutoFilter.ShowAllData
  If Not tDescriptions.DataBodyRange Is Nothing Then tDescriptions.DataBodyRange.Delete
  tDescriptions.ListRows.Add
 
  Set tMouvements = Nothing
  Set tDescriptions = Nothing
End Function
 
Function GetData(Target As Range)
  Dim wbSource As Workbook
  Dim SourceName As String
  Dim shSource As Worksheet
  Dim Source As Range
 
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 1 Then SourceName = .SelectedItems(1)
  End With
  Set wbSource = Workbooks.Open(SourceName)
  Application.ScreenUpdating = False
  Set shSource = wbSource.Worksheets("Interrogation des écritures")
  Set Source = shSource.Range("b19:o" & shSource.Range("b1048576").End(xlUp).Row)
  Target.Resize(Source.Rows.Count, Source.Columns.Count).Value = Source.Value
  wbSource.Close False
  Range("t_Mouvements[Non soldé]").Formula = "=(ABS(INDEX(t_Descriptions2[Solde],MATCH([@[Description 2]],t_Descriptions2[Description 2],0)))>0.05)*1"
End Function
 
Function PrepareData()
  Dim tMouvements As ListObject
  Dim tDescriptions As ListObject
 
  Set tMouvements = Range("t_Mouvements").ListObject
  Set tDescriptions = Range("t_Descriptions2").ListObject
  tDescriptions.ListColumns("Description 2").DataBodyRange.Resize(tMouvements.ListRows.Count).Value = _
    tMouvements.ListColumns("Description 2").DataBodyRange.Value
  tDescriptions.DataBodyRange.RemoveDuplicates 1, xlGuess
  Application.Calculate
  tMouvements.Range.AutoFilter Field:=13, Criteria1:="1"
 
  Set tMouvements = Nothing
  Set tDescriptions = Nothing
End Function

Normalement, on devrait variabiliser certaines choses, mais ça te donne l'idée. On pourrait bien entendu isoler les mouvements lettrés, non lettrés, etc selon la demande. Ici, comme tu récupères les mouvements complets, tu as toutes les infos disponibles en gardant une possibilité de traitement Excel (pas de cellules fusionnées, les données "collent" à celles de départ, ...).

[Novice excel vba]

Bien sûr.
Voilà.

Bonjour,

Je te remercie, je test sur d'autres comptes.

Cordialement,

[Novice excel vba]

Bonjour,

Avec les dates GL
Pièce jointe 601537


Cdlt

Bonjour,

Je te remercie, je test sur d'autres comptes.

Cordialement,

[Novice excel vba]

Jamais très bien compris pourquoi on impose une obligation de moyens plutôt que de résultat. Lorsqu'un comptable veut isoler les mouvements non lettrés, l'important me semble-t-il est qu'il obtienne les mouvements non lettrés et, a priori, de la manière la plus simple possible pour le non-informaticien qu'il est. C'est justement pour cela que Power Query a vu le jour: Libérer les non-informaticiens que sont les utilisateurs d'Excel de la charge de pondre du code... Vu tes demandes d'ajustement, tu n'as pas l'air de comprendre grand chose au code qui t'est donné, sinon tu pourrais l'ajuster toi-même à tes besoins réels. Ca veut dire que tu vas être dépendant de solutions codées que tu maîtrises mal au lieu de viser l'autonomie. Mais bon, soit.

Je me place dans l'optique que ce n'est pas un "one shot" car j'ai démontré que dans ce cas, Excel est nativement outillé pour réaliser manuellement le travail en 2 minutes. Dans le cas où l'on doit répéter l'opération avec des extractions régulières en provenance d'autres sources, alors je préconise de réfléchir "en Excel" de manière à être le moins dépendant possible d'un code VBA incompréhensible pour le non-initié et de:

1. mettre en place un classeur de travail avec les tableaux qui vont bien, déjà préparés et formulés. J'ai pour principe de ne jamais travailler sur l'extraction mais d'importer les données dans un classeur. Ca évite notamment de devoir transférer ton code de fichier d'extraction en fichier d'extraction. Les tableaux structurés créés dans ton fichier de travail vont grandement aider l'écriture du code;
2. Y importer les données par vba, en considérant que l'extraction a toujours la même forme (début à la même ligne et même colonne, soit B18), et feuille nommée toujours de la même façon (ou toujours unique). Ces prérequis sont à adapter à ta situation;
3. Programmer "en VBA pour Excel", c'est-à-dire faire en VBA ce qu'on aurait fait en Excel (copie de la colonne Description2, suppression des doublons, formule de solde non nul);
4. Ecrire un code qui "colle" à ce qui serait réalisé en Excel de manière à être autonome par la suite pour les ajustements et améliorations.

En envisageant la chose de cette façon, j'ai découpé le code VBA selon la finalité de chaque partie (préparation du classeur de travail, récupération des données, traitement des mouvements) de manière à coller aux méthodes Excel et à isoler chaque partie pour pouvoir la tester isolément du reste. C'est la procédure "Process" qui est lancée pour réaliser le traitement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
Option Explicit
 
Sub Process()
  Application.ScreenUpdating = False
 
  PrepareWorkbook
  GetData Range("t_Mouvements")
  PrepareData
 
  Application.ScreenUpdating = True
End Sub
 
Function PrepareWorkbook()
  Dim tMouvements As ListObject
  Dim tDescriptions As ListObject
 
  Set tMouvements = Range("t_Mouvements").ListObject
  tMouvements.AutoFilter.ShowAllData
  If Not tMouvements.DataBodyRange Is Nothing Then tMouvements.DataBodyRange.Delete
  tMouvements.ListRows.Add
 
  Set tDescriptions = Range("t_Descriptions2").ListObject
  tDescriptions.AutoFilter.ShowAllData
  If Not tDescriptions.DataBodyRange Is Nothing Then tDescriptions.DataBodyRange.Delete
  tDescriptions.ListRows.Add
 
  Set tMouvements = Nothing
  Set tDescriptions = Nothing
End Function
 
Function GetData(Target As Range)
  Dim wbSource As Workbook
  Dim SourceName As String
  Dim shSource As Worksheet
  Dim Source As Range
 
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count = 1 Then SourceName = .SelectedItems(1)
  End With
  Set wbSource = Workbooks.Open(SourceName)
  Application.ScreenUpdating = False
  Set shSource = wbSource.Worksheets("Interrogation des écritures")
  Set Source = shSource.Range("b19:o" & shSource.Range("b1048576").End(xlUp).Row)
  Target.Resize(Source.Rows.Count, Source.Columns.Count).Value = Source.Value
  wbSource.Close False
  Range("t_Mouvements[Non soldé]").Formula = "=(ABS(INDEX(t_Descriptions2[Solde],MATCH([@[Description 2]],t_Descriptions2[Description 2],0)))>0.05)*1"
End Function
 
Function PrepareData()
  Dim tMouvements As ListObject
  Dim tDescriptions As ListObject
 
  Set tMouvements = Range("t_Mouvements").ListObject
  Set tDescriptions = Range("t_Descriptions2").ListObject
  tDescriptions.ListColumns("Description 2").DataBodyRange.Resize(tMouvements.ListRows.Count).Value = _
    tMouvements.ListColumns("Description 2").DataBodyRange.Value
  tDescriptions.DataBodyRange.RemoveDuplicates 1, xlGuess
  Application.Calculate
  tMouvements.Range.AutoFilter Field:=13, Criteria1:="1"
 
  Set tMouvements = Nothing
  Set tDescriptions = Nothing
End Function

Normalement, on devrait variabiliser certaines choses, mais ça te donne l'idée. On pourrait bien entendu isoler les mouvements lettrés, non lettrés, etc selon la demande. Ici, comme tu récupères les mouvements complets, tu as toutes les infos disponibles en gardant une possibilité de traitement Excel (pas de cellules fusionnées, les données "collent" à celles de départ, ...).

Bonjour,

Je suis bien d'accord avec toi! vu que je maitrise pas la vba, il est mieux de voir d'autres alternatives comme tu m'as proposé.

De toute façon, je vais en discuter avec les personnes concernés et proposer ton idée sur Power Query.

Je te remercie d'avoir donner ton point de vu et surtout de m'avoir montrer une autre méthode qui s'avère très utile.

Je te tiens au courant pour la suite.

[Novice excel vba]

Bien sûr.
Voilà.

Bonjour,

j'ai testé avec un nouveau compte est je constate 2 anomalies :

- la 1er il ne rapproche pas les écritures avec le même montant, tu pourras t'en apercevoir sur le fichier que j'ai joint.

-2éme les écritures en suspens sont inversés, les débits sont en crédit et vis versa.

Cordialement,

[Pierre Fauconnier]

Bonjour,

j'ai testé avec un nouveau compte est je constate 2 anomalies :

- la 1er il ne rapproche pas les écritures avec le même montant, tu pourras t'en apercevoir sur le fichier que j'ai joint.

-2éme les écritures en suspens sont inversés, les débits sont en crédit et vis versa.

Cordialement,

On va encore tâtonner longtemps pour produire des lignes inutilisables avec les outils natifs d'Excel produites par du code VBA dans lequel une chatte ne retrouverait pas ses jeunes? Tout ça pour un truc qui est résolu en 2 minutes sans Power Query, sans VBA, juste avec trois manips et deux formules... Misère!

[Alex020181]

Pour celui de Alex020181, la macro est bonne mais il me manque certain détail comme d'abord supprimer les valeurs à zéros et aussi indiquer les montants détaillés des écritures en suspens.

- la 1er il ne rapproche pas les écritures avec le même montant, tu pourras t'en apercevoir sur le fichier que j'ai joint.

Heu tu dis qu'il ne rapproche pas les écritures avec le même montant ? C'est normal. Tu m'as demandé précédemment de supprimer les valeurs à 0. Si tu as le même montant (ou même cumul de montants) en D et C alors le solde est 0. Et comme tu voulais les supprimer dans un de tes posts précédents....

Bon. J'ai donc remis les écritures soldées et inversé les D C.

[Novice excel vba]

Heu tu dis qu'il ne rapproche pas les écritures avec le même montant ? C'est normal. Tu m'as demandé précédemment de supprimer les valeurs à 0. Si tu as le même montant (ou même cumul de montants) en D et C alors le solde est 0. Et comme tu voulais les supprimer dans un de tes posts précédents....

Bon. J'ai donc remis les écritures soldées et inversé les D C.

En faite la dernière fois, j'avais demandé de supprimer les valeurs à 0 de la colonne B "Somme D/C", j'ai du mal m'exprimer sur la demande!

Sinon le résultat est impeccable, un grand merci

[Alex020181]

le résultat est impeccable, un grand merci

Cool. Amuse toi bien alors

[Novice excel vba]

Cool. Amuse toi bien alors

Merci, je te tiens au courant pour l'ensemble de mes contrôles

[Novice excel vba]

Cool. Amuse toi bien alors

Bonjour,

j'ai lancé la macro sur plusieurs de mes comptes, il s'avère que cela fonctionne bien.

J'ai comme même des suggestions pour améliorer le fichier :

- Est-il possible de rajouter la description 1 "colonne J" pour les écritures non rapprochées.

- Je constate que la colonne C et D les montants identiques ne se rapprochent pas, malgré que ce sont les mêmes montants, voir l'exemple sur la copie écran envoyé (les montants sont en rouge).

- en terme de présentation comme la suggérer "Alex" pour ce qui est de l'affichage je ne trouve pas que fusionner les dates et les montants dans la même cellule soit le mieux. C'est mieux de séparer

Je vous remercie de votre aide.

Cordialement,

[Pierre Fauconnier]

Je t'ai proposé une solution qui reprend les lignes d'origine non fusionnées justement parce que je trouve qu'il est plus intéressant d'avoir les lignes complètes sans fusionner de données. Tu n'en a pas tenu compte et nous sommes maintenant à une trentaine de messages pour un sujet qui en mérite quatre ou cinq à tout casser.

C'est un peu désespérant

[Novice excel vba]

Je t'ai proposé une solution qui reprend les lignes d'origine non fusionnées justement parce que je trouve qu'il est plus intéressant d'avoir les lignes complètes sans fusionner de données. Tu n'en a pas tenu compte et nous sommes maintenant à une trentaine de messages pour un sujet qui en mérite quatre ou cinq à tout casser.

C'est un peu désespérant

Bonjour,

J'ai bien pris en compte ton fichier que je trouve très intéressant pour mes contrôles que je suis entrain de faire pour mon usage personnel. Mon souci, c'est ma direction qui veut juste mettre en place de la macro ( que je trouve aussi inutile de l'appliquer pour ce genre d'activité).

Je suis désolé de polluer le forum par mes messages...

[Alex020181]

Bojour

Pour faire court;

Bonjour,
- Est-il possible de rajouter la description 1 "colonne J" pour les écritures non rapprochées.
Cordialement,

OUI

Bonjour,
- Je constate que la colonne C et D les montants identiques ne se rapprochent pas, malgré que ce sont les mêmes montants, voir l'exemple sur la copie écran envoyé (les montants sont en rouge).
Cordialement,

Toujours le même problème; dans tes posts précédents un coup tu demandes à ce que les soldes 0 soient visibles un coup tu demandes à les enlever et à les remettre ....

Bonjour,
- en terme de présentation comme la suggérer "Alex" pour ce qui est de l'affichage je ne trouve pas que fusionner les dates et les montants dans la même cellule soit le mieux. C'est mieux de séparer
Cordialement,

D'accord.


Maintenant moi aussi je commence à m'impatienter de clore ce sujet pour parler poliment.

Alors voilà ce que je te propose.
Donne nous un fichier exemple de ton extraction SAP et un fichier exemple d'un traitement manuel de cette extraction contenant tous les cas possibles (comptes soldés, soldes D>C, soldes C>D, mouvements à 0 sur des comptes soldés, mouvements à 0 sur des comptes non soldés, ...) et avec la présentation voulue. (pas besoin de traiter manuellement tous le fichier bien sûr. Prend juste un échantillon parlant)

On partira de là et surtout on s'y tiendra.

Je veux bien donner un coup de main et accepter un peu ensuite les ajustements de la demande mais à un moment faut se fixer.

[Novice excel vba]

Bonjour,

Je comprends tout à fait, mes demandes sont trop nombreuses et on dirait je sais pas ce que je veux...

Maintenant, avec tous les tests que j'ai effectués, je sais quel résultat que je souhaite. Prendre exemple sur le fichier que j'ai envoyé avec comme exemple la description "21153I".


Le fichier contient le 1er onglet l'extraction sur "BO", le 2eme c'est ta Macro et le 3eme onglet c'est le résultat possible.

Merci infiniment de vos contributions!

Cordialement,

[Pierre Fauconnier]

Bo*** de me***

Tu aurais cela en 30 secondes avec POWER QUERY, pas une seconde de plus avec des formules et un filtre, en récupérant toutes les colonnes de ta source. Et ces solutions offrent l'avantage de proposer des données gérables par Excel avec les outils natifs d'Excel


Tu aurais la maîtrise totale de la solution, au lieu d'avoir du code VBA qui fait tout et rien, mais juste pas ce que tu veux. Je ne comprendrai jamais pourquoi on veut réinventer la roue. Combien de fois par jour as-tu besoin de lettrer ces montants pour qu'il soit si impérieux d'automatiser la tâche?

Ce n'est pas une question de polluer le forum. C'est la question d'être pragmatique ou de pondre n'importe quelle solution pour un résultat autre que demandé. Cette discussion est exemplative de la complication qu'on amène sur une question, simple au départ, en répondant à côté d'une question mal posée avec un code VBA alambiqué. En cela, elle mériterait 5 étoiles. Arrêtez de vous compliquer la vie et utiliser ce qu'Excel a à vous proposer avant d'inventer des solutions non demandées à coup de code vba et de cellules fusionnées à des questions mal posées.

Ta question initiale consiste à isoler les mouvements non lettrés et c'est réglé de façon pragmatique en 30 secondes!!

Quelle misère!

[Alex020181]

Donc si je regarde ton exemple partiel:
- pour chaque clé (clé = concaténation de la colonne "Description 2" et "Date comptable" si le montant débit ou crédit <>0) il faut vérifier que la somme des mouvements en débit soit égale à la somme des mouvements en crédit
- ne pas reporter les mouvements soldés de cette clé
- reporter toutes les lignes de cette clé si le solde n'est pas 0

Exact ?

Attention; cela sous-entend que tu n'auras jamais plusieurs fois la même description 2 à la même date comptable ou alors il faut ajouter le montant à la clé mais dans ce cas tu ne pourras plus faire le rapprochement dans le cas où tu as x crédit pour 1 seul débit.

[Novice excel vba]

Donc si je regarde ton exemple partiel:
- pour chaque clé (clé = concaténation de la colonne "Description 2" et "Date comptable" si le montant débit ou crédit <>0) il faut vérifier que la somme des mouvements en débit soit égale à la somme des mouvements en crédit
- ne pas reporter les mouvements soldés de cette clé
- reporter toutes les lignes de cette clé si le solde n'est pas 0

Exact ?

Attention; cela sous-entend que tu n'auras jamais plusieurs fois la même description 2 à la même date comptable ou alors il faut ajouter le montant à la clé mais dans ce cas tu ne pourras plus faire le rapprochement dans le cas où tu as x crédit pour 1 seul débit.

Oui c'est exact, espérons que mon raisonnement fonctionne. Bon courage

[Novice excel vba]

Donc si je regarde ton exemple partiel:
- pour chaque clé (clé = concaténation de la colonne "Description 2" et "Date comptable" si le montant débit ou crédit <>0) il faut vérifier que la somme des mouvements en débit soit égale à la somme des mouvements en crédit
- ne pas reporter les mouvements soldés de cette clé
- reporter toutes les lignes de cette clé si le solde n'est pas 0

Exact ?

Attention; cela sous-entend que tu n'auras jamais plusieurs fois la même description 2 à la même date comptable ou alors il faut ajouter le montant à la clé mais dans ce cas tu ne pourras plus faire le rapprochement dans le cas où tu as x crédit pour 1 seul débit.

Bonjour,

Je me permets de revernir vers toi concernant la macro, je voulais savoir si tu as eu le temps ou la possibilité de le faire.

Je te remercie par avance,

Cordialement,