Remplacements de masse
Bonjour à tous,
Je travaille dans un cabinet comptable. Mes collègues travaillent avec des fichiers excel qui leur permettent d'intégrer les écritures comptables dans le logiciel utilisé dans le cabinet.
Ces fichiers de trame ont été élaborés par l'éditeur du logiciel. Ils ont été créés pour intégrer 102 écritures au maximum. La plupart de mes collègues les utilisent pour intégrer des fichiers de caisse qui comportent une écriture par jour sauf que certains les utilisent pour intégrer des fichiers d'encaissement qui enregistrent tous les encaissements du mois. J'ai travaillé sur un fichier qui faisaient près de 800 lignes.
Le fichier d'origine subit divers traitements avant d'être intégré et rajouter les 700 lignes à la main étaient franchement fastidieux. J'ai donc essayé d'automatiser.
Voilà une bonne semaine que j'écume les forums et les cours de VBA pour arriver à traiter mon fichier en automatique et je remercie particulièrement ce site qui m'a beaucoup aidée.
Je suis presque arrivée à mes fins.
J'ai une première feuille à laquelle j'ai rajouté 700 lignes et une autre feuille qui récupère au travers de divers formules les infos de la précédente. Cette feuille travaille par pavé de 16 lignes en faisant appel à chaque ligne de la feuille précédente.
Quand on fait un copier-coller dans cette feuille d'un pavé, la zone est incrémentée de 16. J'ai donc tenté de faire des rechercher-remplacer (303 valeurs au total à remplacer dans le pavé) dans chaque pavé. Au départ tout semble bien fonctionner même si le fonctionnement se fait à l'envers (797, 796, 795 au lieu de 103, 104, 105), mais au bout d'un moment ça beuggue et le remplacement semble un peu fantaisiste puis redevient normal. J'ai tourné le problème un peu dans tous les sens mais je ne comprends pas les raisons de ce bug.
Je suis arrivée au bout de mes limites, je m'en remets à vous. Je vous remercie de votre aide.
Valérie
Bonsoir Vallac,
Je suis sur votre fichier actuellement et j'essai de faire votre procédure mais certains fichiers me manquent.
Pourriez-vous m'expliquer pas à pas ce que vous voulez que je fasse pour vous ?
PS : Plein de choses sont améliorable sur votre fichier (il y a vraiment trop de formule, ce qui alourdis énormément le fichier)
Je peux vous aider en VBA.
J'attend votre retour
Bonjour Pat1308,
Merci de vous pencher sur mon problème.
Donc j'ouvre le fichier trame puis le fichier d'export et j'applique ce code :
Sub procedure2()
'
' procedure2 Macro
'
'
'traitement du fichier de base
'variable qui copie tant qu'il y a des lignes
DL = Cells(Application.Rows.Count, 1).End(xlUp).Row
Columns("I:I").Select
selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").Select
ActiveCell.FormulaR1C1 = "jour"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=DAY(RC[1])"
Range("I2").Select
selection.NumberFormat = "dd"
selection.AutoFill Destination:=Range("I2:I" & DL)
Range("I2:I" & DL).Select
Columns("R:R").Select
selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R1").Select
ActiveCell.FormulaR1C1 = "compte"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[1],1,3)"
Range("R2").Select
selection.AutoFill Destination:=Range("R2:R" & DL)
Range("R2:R" & DL).Select
Dim DLig As Long
DLig = Range("A1").End(xlDown).Row
Range("A1:AK" & DLig).Select
selection.Copy
'Traitement de la trame
Windows("Trame Excel - Ventes.xlsm").Activate
Sheets("fichier").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("fichier").Activate
For Ligne = Range("A1").End(xlDown).Row To 2 Step -1
If Ligne > 102 Then
Sheets("SAISIE").Activate
Rows("111:111").Select
selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End If
Next Ligne
Dim DerLigC As Long
DerLigC = Range("B" & Rows.Count).End(xlUp).Row - 1
Set SourceRange = Worksheets("SAISIE").Range("B110:AF110")
Set fillRange = Worksheets("SAISIE").Range("B110:AF" & DerLigC)
SourceRange.AutoFill Destination:=fillRange
'Traitement feuille intégration
End SubA la fin du code, les données d'export sont collées dans la feuille "fichier" du fichier Trame et reportées dans la feuille "SAISIE". J'ai bien conscience que le code n'est pas optimal puisque j'ai utilisé l'enregistreur de macro (je débute complètement en VBA).
Donc jusqu'ici, j'obtiens le résultat souhaité.
Ensuite j'applique le code suivant :
Sub INTEGRATION2()
'
' INTEGRATION2 Macro
'
Sheets("fichier").Activate
Dim nbLignes As Integer
nbLignes = Range("A1", selection.End(xlDown)).Cells.Count * 16 + 110
Dim Ligne As Integer
Dim numLigne As Integer
For Ligne = Range("A1").End(xlDown).Row To 2 Step -1
If Ligne > 110 Then
numLigne = Ligne
Sheets("INTEGRATION").Activate
Sheets("INTEGRATION").Rows("1602:1617").Copy
ActiveCell.Offset(16, 0).Select
ActiveSheet.Paste
Dim X As Long
X = 126
For X = 126 To nbLignes Step 16
selection.Replace What:=X, Replacement:=numLigne, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next X
End If
Next Ligne
Dim DLig As Long
DLig = Range("A1").End(xlDown).Row
Range("A1618:A" & DLig).Select
selection.ClearContents
Range("A1602:A1617").Copy ' copie de la ligne 1 de la feuille 1
ActiveSheet.Paste
ActiveWindow.DisplayFormulas = False
Range("A1:J" & DLig).Copy
End Subpour copier coller les pavés de formules utilisés dans la feuille "INTEGRATION". Je veux intégrer les lignes supplémentaires de "fichier" de la ligne 103 à la fin du fichier. Et c'est là que ça se corse puisque les copiers-collers sont incrémentés d'un pas de 16 et que je veux que les formules appellent les lignes de la feuille "SAISIE" à partir de la ligne 111.
Mes copiers-collers remplacements se font correctement au départ puis ajoutent des 2 soit à la fin comme dans la copie d'écran
(7162 au lieu de 716)
ou au début comme ici 2671 au lieu de 671 etc
Merci encore pour votre aide et toutes les propositions sont les bienvenues pour améliorer tout ça.
Bonne journée à tous,
Valérie (VBA grand débutant)
Bonjour Valérie,
J'ai travaillé pas mal sur votre fichier et je suis pour l'instant arrivé à l'étape 5 de votre procédure qui me demande "la trame vente briois" donc j'étais un peu coincé.
J'analyse votre code et le refait pour que cela soit plus rapide et plus efficient pour vous.
Je vous renvoi un message avec le fichier dès que j'aurais terminé.
A tout à l'heure
Patrice
Valérie,
Pourriez-vous m'expliquez cette partie de code :
Sheets("fichier").Activate
For Ligne = Range("A1").End(xlDown).Row To 2 Step -1
If Ligne > 102 Then
Sheets("SAISIE").Activate
Rows("111:111").Select
selection.Insert Shift:=xlDown
Application.CutCopyMode = False
End If
Next Ligne
Dim DerLigC As Long
DerLigC = Range("B" & Rows.Count).End(xlUp).Row - 1
Set SourceRange = Worksheets("SAISIE").Range("B110:AF110")
Set fillRange = Worksheets("SAISIE").Range("B110:AF" & DerLigC)
SourceRange.AutoFill Destination:=fillRange
'Traitement feuille intégrationPourquoi If Ligne > 102 ?
Pourquoi ajouter plus de 600 insertion de lignes dans la feuille SAISIE ?
A quoi servent SourceRange et fillRange ?
Merci pour votre aide
PS : Je viens de comprendre pourquoi, désolé du dérangement
Bonjour Pat,
Désolée pour le "Briois" qui traînait, mes collègues renomment leur trame en fonction du client et j'étais partie de ce client.
Comme les fichiers d'encaissements varient d'un mois sur l'autre, j'ai essayé d'ajouter le nombre de lignes pour intégrer tous les encaissements. Ces trames ont été créées à l'origine pour intégrer 102 lignes (me demandez pas pourquoi, c'est purement arbitraire à mon sens, les équipes qui ont réalisé le fichier devaient penser que ça suffirait à répondre aux besoins)
Merci beaucoup pour votre aide,
Valérie
Bonjour Valérie,
Désolé du temps d'attente, j'ai due m'absenter.
Je vous fournis le fichier avec la première partie.
Il ne contient pas encore l'intégration mais j'espère le réaliser sous peu.
Bonne journée
Patrice
Bonjour Pat1308,
Merci beaucoup pour le temps que vous me consacrez.
Bon week-end à vous,
Valérie
Je vous en prie, c'est normal.
Bon week-end à vous aussi.
J'essai de vous faire un retour rapidement.
Patrice
Pat1308,
J'ai étudié la première partie du code et je l'ai appliqué, ça marche nickel !
J'ai apporté une modif au niveau des comptes parce que ma collègue avait une demande particulière (habituellement on prend les 3 premiers caractères mais pour ce dossier, ça créerait trop de comptes et ça deviendrait ingérable) et j'ai rien cassé, ça continue de fonctionner
D'ici que j'arrive à écrire un code propre comme celui-là de l'eau va couler sous les ponts
Bon dimanche à tous,
Valérie
Valérie,
Chacun à sa propre façon de coder mais c'est très bien que vous vous soyez amélioré à la lecture de mon code (en plus, je ne l'avais pas beaucoup commenté donc vous pouvez être fier de vous).
Je reviens vers vous rapidement avec les pas de 16 ^^
Bonsoir à tous,
J'ai passé la semaine à tester diverses possibilités mais je tourne toujours en rond. J'ai fait pleins de tests avec des MsgBox, je récupère les bonnes valeurs et dès que ça passe dans les remplacements , le bug se reproduit. Je ne comprends pas comment ça peut m'afficher la bonne valeur et remplacer par une mauvaise
Si quelqu'un a une idée, je suis preneuse.
Bon week-end à tous,
Valérie
bonjour à tous,
Je travaille toujours sur mes fichiers de trame, cette fois, j'ai dû rajouter des colonnes à ma feuille de saisie (20 colonnes entre P et Q) et donc insérer des lignes dans la feuille INTEGRATION (20 lignes entre la ligne 13 et la ligne 14 pour le 1er pavé de formules). J'ai recopié la ligne au-dessus mais la recopie incrémente les lignes et non les colonnes.
Je cherche donc à remplacer tous les P26, P27, P28 par Q25, R25, S25. J'ai tourné le problème un peu dans tous les sens mais je n'ai pas trouvé d'autres solutions que de le faire à la main (j'ai rajouté 20 lignes dans 100 pavés...)
J'ai juste fait ça :
Range("B554:J573").Select
Selection.Replace What:="P26", Replacement:="Q25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P27", Replacement:="R25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P28", Replacement:="S25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P29", Replacement:="T25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P30", Replacement:="U25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P31", Replacement:="V25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P32", Replacement:="W25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P33", Replacement:="X25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P34", Replacement:="Y25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P35", Replacement:="Z25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P36", Replacement:="AA25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P37", Replacement:="AB25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P38", Replacement:="AC25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P39", Replacement:="AD25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P40", Replacement:="AE25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P41", Replacement:="AF25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P42", Replacement:="AG25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P43", Replacement:="AH25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P44", Replacement:="AI25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="P45", Replacement:="AJ25", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=FalseDonc je reprends le bout de code et je modifie les références des cellules pour passer au pavé suivant.
Y aurait-il une autre façon de faire plus rapide ?
Je vous remercie de votre aide,
Bonne journée à tous.
Valérie
Bonjour Valérie,
Excusez moi de n'avoir pu vous répondre avant.
Pourriez-vous m'expliquez ce qu'il doit être faire dans l'intégration (sans rentrer dans la décomposition que vous avez créé avec des formules et des copier/coller car cela devient très difficile à lire et donc à coder en VBA) ?
Les bouts de code que vous additionnez sont issus de l'enregistreur de macro (cela se voit aux code que vous venez de fournir) et cela complique énormément l'aide que je pourais vous apporter, je m'explique :
Excel via l'enregistreur de macro crée du code pièce par pièce alors que normalement, il faut traiter le code VBA dans sa globalité.
Traiter dans sa globalité permettrait un codage plus simple et surtout une plus grande rapidité d'execution ainsi, pourriez-vous m'expliquer ce que vous tentez de réaliser svp ?
J'essayerais de me rendre un peu plus disponible afin d'en discuter et ainsi de finaliser votre projet à l'avenir.
N'hésitez pas.
Patrice
Bonjour Patrice,
Ne vous excusez pas, c'est moi qui vous remercie pour le temps que vous m'accordez.
Comme je l'ai expliqué ces fichiers nous viennent de notre prestataire. C'est le prestataire qui a créé toutes les formules et j'ai essayé de faciliter la vie de mes collègues en modifiant ces fichiers pour les adapter à leurs besoins actuels.
Je me suis débrouillée pour l'insertion des colonnes, ça m'a pris du temps mais comme ça ne bougera pas, je vais laisser ça comme ça pour l'instant. Ma patronne m'a tannée donc j'ai fait comme j'ai pu
Effectivement, il faudrait tout reprendre et analyser toutes les formules qui font appel à la feuille de saisie. Je me pencherai sur la question quand j'aurai du temps. Dans mon idée, ce serait un bouton qui permet d'insérer colonne par colonne selon le besoin et qui se charge d'insérer les lignes dans les pavés de la feuille d'intégration. Mais je préfère gérer l'urgence pour le moment.
Donc Mon besoin porte toujours sur ma demande de départ et l'ajout de lignes pour intégrer les fichiers d'encaissements. Si on reprend, pour la feuille appelée SAISIE, on est ok (j'ai rajouté des petites choses comme le traitement des comptes clients, des calculs portant sur les différences avec les montants et les calculs de TVA, ça porte sur des centimes mais il faut l'intégrer dans la compta ) mais je bloque sur le problème dans la feuille d'intégration qui ne s'incrémente pas de la bonne façon quand je rajoute des pavés correspondant aux lignes qu'on a ajoutées, avec mes remplacements qui se font de façon fantaisiste.
Je vous remercie beaucoup pour votre aide.
Bon week-end,
Valérie
Bonjour Valérie,
l'erreur se trouve ici :
For X = 126 To nbLignes Step 16
Selection.Replace What:=X, Replacement:=numLigne, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next XVous remplacez dans les formules les informations de 126 à 1710 par tranche de 16 mais attention, il remplace absolument toutes les valeurs même celles qui contiennent le chiffre X.
Voilà pourquoi les formules se transforment de façon fantaisiste.
Merci Patrice de tes éclaircissements. Est-ce qu'il faut que je récupère la référence de la cellule soit SAISIE!B126 pour effectuer mes remplacements ?
Si tel est le cas, je n'ai pas la moindre idée de comment faire, en plus j'ai la grippe et je tourne au ralenti
J'ai tenté quelquechose mais le temps d'exécution est tellement long que ce n'est sûrement pas la bonne méthode.
Valérie,
Aïe ... Soignez vous bien.
Concernant le remplacement, je n'ai aucune idée de ce que cela devrait donné normalement donc je n'ai aucune idée de comment réparer cette partie du code.
De plus, la lenteur viens des copier/coller et des remplacements de formule qui ne sont pas optimisés par Excel en temps normal, c'est pour cela que je vous ai demandé ce que cela devait donner dans mon précédent poste ....
Auriez-vous le résultat de ce que cela devrait donner normalement ?
Si je comprends bien le sens de la question (j'en suis pas sûre
Si on reprend le fonctionnement de cette trame :
1) dans la feuille de saisie, on récupère le fichier d'encaissement qu'on intègre :
2) dans la feuille d'intégration, il y a des formules qui font appel à la feuille de saisie
Les valeurs sont copiées dans le logiciel de compta.
Si on copie colle les pavés tel quels, les formules sont incrémentées des 16 lignes que contiennent les pavés.
Mais moi je veux que le pavé fasse appel à la ligne suivante, soit 111 au lieu de 127, 112 au lieu de 144 etc. (j'ai ajouté une ligne blanche pour la lisibilité du fichier, donc pas de 17 au lieu de 16)
J'espère avoir répondu à la question
Bonjour Valérie,
Ok, et les SUPPR vous servent à quoi dans la feuille d'intégration ?
Sont-ils obligatoire ?
Que fais-tu des Avoirs dans ton journal (actuellement ils s'affichent en moins mais dans la même colonne que les factures) ?
Vas-tu compléter les factures avec d'autres numéros de compte ?
Ne faudrait-il pas que les facture soit trié par date et par client ?
D'après votre exemple d'intégration et de saisie, je ne dois pas avoir un fichier complet car il me manque les 0.28 de la colonne Cpte2 dans la feuille de saisie et donc par la même occasion les formules sur les produits divers et autre.