Lettrage comptable
Bonjour,
Je souhaite écrire une macro VBA qui permet de réaliser le lettrage des montants
Débit et Credit sur un MONTANT identique.
Tout en utilisant le Code AAA pour le premier lettrage, AAB, AAC…
J'avais effectué un macro précédemment mais celle ci ne fonctionne pas ...
D'avance merci
"Sub Lettrage()
Dim compte As String
Dim compare As String
Dim codelet As String
c1 = 65
c2 = 65
c3 = 65
codelet = Chr(c1) + Chr(c2) + Chr(c3)
Application.Range("A2").Activate
compte = ActiveCell.Value
While ActiveCell <> ""
ActiveWorkbook.Names.Add Name:="position", RefersToR1C1:=ActiveCell
ActiveCell.Offset(0, 5).Activate
debit = ActiveCell.Value
If debit = "" Then GoTo suite
Range("G2").Activate
compare = ActiveCell.Offset(0, -6).Value
While compare <> compte
ActiveCell.Offset(1, 0).Activate
If ActiveCell = "" Then GoTo fin
Wend
credit = ActiveCell.Value
Do
If ActiveCell.Offset(0, -6) <> compte Then GoTo fincpte
If debit = credit Then
ActiveCell.Offset(0, -2).Value = codelet
Application.Range("position").Activate
ActiveCell.Offset(0, 4).Value = codelet
Application.Range("position").Activate
c3 = c3 + 1
If Chr(c3) > "Z" Then
c3 = 65
c2 = c2 + 1
End If
If Chr(c2) > "Z" Then
c3 = 65
c2 = 65
c1 = c1 + 1
End If
codelet = Chr(c1) + Chr(c2) + Chr(c3)
GoTo suite
End If
ActiveCell.Offset(1, 0).Activate
credit = ActiveCell.Value
Loop
fincpte:
compte = ActiveCell.Offset(0, -6)
Application.Range("position").Activate
suite:
ActiveCell.Offset(1, 0).Activate
Wend
fin:
End Sub
Mon erreur provient du fait que lorsqu'un montant au débit ne figure pas au crédit, il le garde en mémoire et le compare avec le montant au débit qui le précède ...
Bonjour
Je ne sais pas si tu as réussi
Dans le cas contraire
Tu devrais mettre un exemple du résultat que tu souhaites
Car avec ton explication (et même ton code) pas facile de comprendre
Bonsoir,
A tester :
Sub lettrage()
Dim i&, k&, kk&, wL$, N%, tmp%, Tablo(), Y As Boolean, vMontant
i = Cells(Rows.Count, 1).End(xlUp).Row
Tablo() = Range(Cells(1, 5), Cells(i, 7))
'Détermination du dernier lettrage utilisé
For k = 2 To i
wL = Tablo(k, 1)
If wL <> "" Then
tmp = URAP(wL)
N = IIf(tmp > N, tmp, N)
End If
Next
'Lettrage
N = N + 1
For k = 2 To i
If Tablo(k, 1) = "" And Tablo(k, 2) > 0 Then
vMontant = Tablo(k, 2)
For kk = 2 To i
Y = Cells(k, 1) = Cells(kk, 1)
If Y And Tablo(kk, 3) = vMontant Then
wL = RAP(N)
Tablo(k, 1) = wL
Tablo(kk, 1) = wL
N = N + 1: Exit For
End If
Next
End If
Next
'Affichage
Range(Cells(1, 5), Cells(i, 7)) = Tablo()
End Sub
Function RAP(ByVal x&) As String
'Codage sur 3 lettres
Dim i&, j&, k&
x = x - 1
i = Int(x / 676)
j = i * 676
k = Int((x - j) / 26)
RAP = Chr$(65 + i) & Chr$(65 + k) & Chr$(65 + x - j - k * 26)
End Function
Function URAP(z$) As Integer
'décodage
Dim i%, j%, k%
i = (Asc(Left(z, 1)) - 65) * 676
j = (Asc(Mid(z, 2, 1)) - 65) * 26
k = (Asc(Right(z, 1)) - 65)
URAP = i + j + k + 1
End FunctionA+[post-it]Code et fichier échangés après simplification (optimisation)[/post-it]
Bonsoir
En regardant le résultat de la macro de galopin01 (que je salue) j'ai imaginé une autre solution mais avec des résultats différents
A vérifier
Bonsoir/bonjour
Je souhaite relancer ce sujet un an après.
J'ai trouvé la solution de galopin01 très utile. Néanmoins je souhaite solliciter à mon tour cette personne (ou tout autre qui pourrait m'aider)
J'ai essayé de comprendre sa macro afin de l'adapter à mes besoins, mais trop complexe pour mon niveau (d'ailleurs, au passage, s'il est disponible j'apprécierai lui poser quelques questions pour comprendre la structure de la macro)
Vous trouverez sur la seconde feuille du classeur joint, les colonnes en bleues. je cherche a ce que le lettrage se fasse comme avec la solution de galopin01 mais, cette fois, en prenons aussi en compte le nom de l'auxiliaire, la référence de la facture et bien sur les montants en débit/crédit. (pour éviter les lettrages entre différents tiers ou pièce d'un même montant)
A titre d'exemple, le lettrage du tiers agraphor doit s'effectuer contrairement a celui du tiers baboto
Vous remerciant,
Bonjour,
Le lettrage s'applique à des Débits/Crédits identique.
Compte tenu de la diversité des cas possibles il n'est pas possible de travailler sur des regroupements (plusieurs Débits compensés par un seul Crédit ou vice versa)
Le fichier modifié pour tenir compte de ta demande.
La première ligne sert à Typer les variables Y, Y1 et Y2 (As Boolean)
J'ai juste modifié juste la plage de travail et la condition : Y devient :
Y = Y1 And Y2Edit : J'ai rajouté lettrage_G2.xls ou j'ai intégré la comparaison des N° de pièces qui m'avait échappée ce matin.
A+
Bonsoir galopin,
J'ai pas eu le temps de revenir avant,
Ta réponse convient parfaitement à mon problème.
Par contre je suis toujours curieux de mieux comprendre tes macros. C'est pourquoi j'ai quelques questions :
Est ce que les signes & et $ ont une signification particulière quand tu déclares tes variables (je connais le & dans les formules mais pas dans la notion de variable) ?
Peux tu me décrypter ce bout de code de ta macro sub lettrage ()
N = N + 1: Exit For (le ": exit for" me perturbe dans la syntaxe - jamais vu)
Finalement tu utilise une valeur 676 avec laquelle tu fais des calculs, peux tu m'expliquer a quoi correspond cette valeur ?
grand merci pour ta réponse qui convient déjà parfaitement à ton travail.
Bonsoir galopin,
J'ai pas eu le temps de revenir avant,
Ta réponse convient parfaitement à mon problème.
Par contre je suis toujours curieux de mieux comprendre tes macros. C'est pourquoi j'ai quelques questions :
Est ce que les signes & et $ ont une signification particulière quand tu déclares tes variables (je connais le & dans les formules mais pas dans la notion de variable) ?
Peux tu me décrypter ce bout de code de ta macro sub lettrage ()
N = N + 1: Exit For (le ": exit for" me perturbe dans la syntaxe - jamais vu)
Finalement tu utilise une valeur 676 avec laquelle tu fais des calculs, peux tu m'expliquer a quoi correspond cette valeur ?
grand merci pour ta réponse qui convient déjà parfaitement à ton travail.
J'apporte un erratum,
J'ai compris le 676 (carré de 26), c'est le 65 que je ne comprend pas.
Bonjour,
Merci pour ce retour.
Dans les déclarations
%, &, $ et # sont des raccourcis d'écriture
& est l'équivalent de "as Long"
$ est l'équivalent de "as String"
% est l'équivalent de "as Integer"
# est l'équivalent de "as Double"
Ces raccourcis sont pratiques quand on utilise un grand nombre de variables.
Il n'existe pas de raccourcis pour les autres type de variables mais... il existe des "DefType" (très méconnus) qui permettent de définir "en bloc pour toutes les procédures" des variables qui commencent par certaines lettres.
C'est le cas par exemple de DefBool qui défini comme Booléenne toute variable dont la première lettre est un Y...
ça évite ainsi une longue suite de répétition quand on déclare ensuite une longue suite de
Dim Y as Boolean , Y1 as Boolean, Y2 as Boolean...(Voir dans VBA l'aide de DefBool pour trouver la liste de tous les DefType)
" : " dans le code est l'équivalent d'un retour ligne. Ainsi j'aurai pu écrire également
N = N + 1
Exit ForCe qu'on pourrait traduire par :
puisqu'on a trouvé une condition satisfaisante inutile de continuer à parcourir la liste :
on incrémente pour la prochaine numérotation (donc on sort de la première boucle For) et on continue de parcourir les autres éléments du Tablo().
676 est le carré de 26... (AZZ)
A+
Bonjour
Je voudrais vraiment vous dire un grand merci mais profiter pour vous demander si c'est possible de trouver un open source qui effectue ce travail ?
A+
Bonjour,
Je ne sais pas ce que tu entends par là, mais ces macros dont j'ai écrit le code peuvent être utilisé librement.
Il en existe même une version à quatre caractères (sur demande) permettant de rapprocher près de 30 000 lignes !
(très pratique également pour coder avec des clefs d'index alphanumérique courtes...)
A ma connaissance seuls les logiciels comptables font ce travail pour les rapprochements simples.
Quelques logiciels comptables haut de gamme sont capables de lettrage complexe avec vérification de référence.
Je n'en connais aucun qui soit capable de traiter des regroupements multiples. (Ce qui ne signifie nullement que c'est impossible... mais conceptuellement cela me parait improbable.)
Nota : 65 est le N+ ASCII de la lettre A
A+
bonjour GALOPIN
apres le letrrage automatique par montant en AAA ou AAB, lesregroupements n'etant pas pris en compte,
je peux le faire manuellement , donc je souhaiterais avoir des case a coher devant chaques Operations
que j'aurai pu matcher mais , je voudrais que c'est operations soite inserer dans ma base des operations matchées
avec une conotation commencant par M (M999999) donc en definitif j'aurai dans la base des operations matchées
celle en AA% et M% ce qui me pousserait plus tard a faire une requete me permettant de les distinguer.
Merci pour votre aide tres precieuse
Bonjour,
Il n'est pas vraiment nécessaire de créer une nouvelle colonne ni des cases à cocher on pourrait très bien continuer le lettrage manuel
dans la colonne lettrage en tapant des "x" par exemple le système est très capable de détecter des sommes nulles quand tu as plusieurs "x" mais ce qui me gêne le plus pour te faire ça c'est le manque de données valides dans ton fichier joint.
J'ai donc repris ton ancien classeur aménagé pour te faire un exemple.
Les cellules vertes pourraient faire l'objet d'un lettrage manuel.
Si tu clicques dans ces cellules vertes elles sont cochées quand toutes les cellules sont cochées la somme est nulle et les lignes sont lettrées : C'est la colonne K qui est alors cochée (pour différencier le lettrage manuel...)
Nota : il est possible de décocher, voire de délettrer une cellule cochée en recliquant dessus. (uniquement pour le lettrage manuel...)
Attention toutefois au délettrage car aucun contrôle de cohérence n'est effectué : Lors d'un délettrage c'est à toi de t'assurer que ton délettrage est à somme nulle...
Attention ! Bien que ce soit possible, s'agissant d'un lettrage manuel, aucun autre contrôle de cohérence n'est effectué au moment du lettrage : il s'effectue seulement sur la base de sommes nulles. Contrairement au lettrage automatique, c'est donc à toi de t'assurer que les références pièces ou auxiliaire sont Ok...
A+
bonjour
Merci Galopin j'ai bien compris le principe. Pour que tu puisses bien cerner le fichier comme tu la demandé
je joins un fichier complet
Merci vraiment
bonjour,
Ce fichier est aberrant ! Ce lettrage ne ressemble à rien du tout :
Exemple : AAB (ligne 8 et 96) sont lettrés ensemble mais sont tous 2 au Débit !
Et les exemples de ce genre sont multiples...
La conception comptable du lettrage est de rapprocher des montants à somme nulle.
Débit = Crédit
MA réponse précédente est basée sur l'utilisation de MA macro de lettrage.
Dans les conditions actuelles je ne peut pas assurer la continuité.
A+
Bonjour Galopin
j'avais remarqué ce probleme raison pour laquelle je vous ai envoyé afin que vous me proposiez un bon code de lettrage et resolvez mon probleme qui me stresse je vous l'assure.
SVP soyez indulgent et faites le pour moi
bonjour,
Une autre fois donnez directement des fichiers vraisemblables ça évitera de travailler pour rien...
Je n'ai pas été capable de détecter de lettrage à faire manuellement.
Aussi pour tester j'ai créé manuellement 3 lignes lettrables (en vert colonne C lignes 6, 100 et 101)
Il suffit de cliquer dans les 3 cellules marquées pour les cocher.
Quand les 3 cellules sont cochées la somme est nulle alors elles sont lettrées (et cochées en colonne K pour vous permettre de les distinguer facilement par la suite)
Relire plus haut mes observations concernant le décochage et le délettrage.
A+
Bonjour galopin,
je suis tombé sur votre macro Excel Lettrage_G. elle m'a était très utile, merci beaucoup.
Je dois effectuer un lettrage entre le compte 512 banque et le relevé bancaire sur presque une centaine d'écritures comptables.
Pouvez-vous s'il vous plais adapter votre macro lettrage_G au fichier excel ci-joint.
cahier des charges :p
- lettrage colonne C (débit) avec colonne j (crédit)
- lettrage colonne D (crédit) avec colonne i (débit)
- est-il possible d'y ajouter du code pour lorsqu'une écriture n'est pas lettré la case ou ligne apparait dans une couleur différente par exemple.
encore merci pour votre aide
bonne journée.
Bonjour,
Votre tableau n'est vraiment pas adapté à faire du lettrage.
Moyennant un petit bricolage vous pouvez quand même l'utiliser sur une feuille de travail en vous basant sur mon modèle.
- Suppression de la première ligne
- Recopie du tableau de droite (seulement les données) sous le tableau de gauche.
- Lancement de la macro
- Recopie de la partie basse à son emplacement d'origine...
Attention : La dernière ligne du tableau de droite doit impérativement comporter une date. (sinon la ligne ne serait pas prise en compte par la macro)
Il ne m'a pas semblé utile de colorer une ligne non lettré : Le programmeur est souvent un peu du genre fainéant
A+
bonsoir galopin01,
comment faites-vous a partir du tableau a gauche pour insérer de nombreuses écritures comptables ainsi que en bas a gauche car malheureusement pour moi cela fonctionne pas ;(
je vous mets les 2 fichiers a lettré .
Feuille 1 tableau a droite relevé bancaire
feuille 2 tableau compte 512 banque de la comptabilité
pouvez-vous m expliquer la manière de procéder.
encore un grand merci