XL 2021 Complétion d'un tableau automatisée

VincentH.DR

XLDnaute Nouveau
Bonjour à tous,

J'aurai besoin des lumières de la communauté.

J'ai créé un fichier pour de la comptabilité et de l'audit et n'étant pas très bon en VBA, je bloque souvent lors de l'écriture des macros.

Pour ceux à qui cela parle, pour les variations des immobilisations et des amortissements, un tableau doit être compléter et joins à l'annexe.
Dans ce tableau nous avons deux parties :
- la valeur brute (N-1) des biens auxquelles on va ajouter les acquisitions et soustraire les cessions et ainsi obtenir la valeur brute (N).
- le cumul des amortissements de N-1 auquel on ajoute les dotations de l'année et l'on soustrait les reprises pour obtenir le cumul N

La valeur brute moins le cumul des amortissements donne alors la valeur nette comptable.

Dans la balance comptable, chaque bien ou catégorie de bien est identifiée par un compte spécifique.

La macro que j'ai est la suivante, je ne l'ai pas créée, je me suis inspiré que ce qui se faisait sur les forums et l'ai adaptée à mes besoins (la partie qui ne convient pas est en rouge) :

Dim ws, wsSource As Worksheet

Dim rgRecherche As Range

Dim cell, res As Range



Set ws = ActiveSheet

Set res = ws.Range("B9") 'Le résultat sera présenté à partir de la cellule A1 de la feuille actuelle



Set wsSource = Sheets("Balances") 'Source de données dans la feuille CONVERT

Set rgRecherche = wsSource.Range("A7:B10000") 'Critère de recherche : colonne A de CONVERT





ws.Range("B9:C50").ClearContents ' Suppression des résultats précédents

For Each cell In rgRecherche ' Recherche dans chaque cellule de la colonne A

If Left(cell, 2) = "20" Or Left(cell, 2) = "21" Or Left(cell, 2) = "23" Or Left(cell, 3) = "274" Or Left(cell, 3) = "275" Or Left(cell, 3) = "276" Then

res.Value = cell.Value ' Reporte le N° compte

res.Offset(0, 1).Value = cell.Offset(0, 1).Value ' Reporte le libellé de compte

res.Offset(0, 1).Value = cell.Offset(0, 1).Value ' Reporte le libellé de compte

Set res = res.Offset(1, 0) ' Retour à la ligne suivante pour la prochaine écriture à extraire

End If

If IsEmpty(cell) Then Exit For ' Fin de la recherche dès la première cellule vide (DANGER : il faut que l'import ne présente aucune ligne intermédiaire vide)

Next
Set ws = ActiveSheet

Set res = ws.Range("H9") 'Le résultat sera présenté à partir de la cellule A1 de la feuille actuelle



Set wsSource = Sheets("Balances") 'Source de données dans la feuille CONVERT

Set rgRecherche = wsSource.Range("G7:H10000") 'Critère de recherche : colonne A de CONVERT





ws.Range("H9:J50").ClearContents ' Suppression des résultats précédents

For Each cell In rgRecherche ' Recherche dans chaque cellule de la colonne A

If Left(cell, 2) = "28" Then

res.Value = cell.Value ' Reporte le N° compte

res.Offset(0, 1).Value = cell.Offset(0, 1).Value ' Reporte le libellé de compte

res.Offset(0, 2).Value = cell.Offset(0, 2).Value ' Reporte le libellé de compte

Set res = res.Offset(1, 0) ' Retour à la ligne suivante pour la prochaine écriture à extraire


End If

If IsEmpty(cell) Then Exit For ' Fin de la recherche dès la première cellule vide (DANGER : il faut que l'import ne présente aucune ligne intermédiaire vide)

Next

Dim cel As Range
For Each cel In Range("B9:B50")
If cel = 0 Then cel.EntireRow.Hidden = True
Next cel


Lorsque je lance la macro, le tableau suivant est alors complété :


1687547741811.png


Ce qui ne convient pas c'est que les comptes des catégories de biens ne sont pas en alignement avec les comptes d'amortissements.
Il faudrait que le tableau qui ressort une fois la macro lancée donne :

1687547805855.png


Comme vous pouvez le constater, dans la colonne des valeurs d'ouverture et de clôture, les montants ne sont plus les mêmes.

Second problème, c'est que certains comptes comme le 207000, ne sont pas amortissables, il n'y a alors pas de compte d'amortissement qui peut aller en face c'est pour cela qu'il y a un blanc dans les amortissements.

J'avais pensé à insérer le chiffre 8 en seconde position (qui comptablement signifie l'amortissement) mais il fallait ensuite que je fasse une condition qui aurait masqué la ligne si c'était un compte qui n'était pas amortissable.

Merci et ceux/celles qui prendront le temps pour me répondre.
En vous souhaitant un bon weekend,

Vincent.
 
Solution
Bonjour,

J'ai fini par trouver la solution à travers une formule et aussi grâce à un autre site qui avait une partie de la solution ( https://fr.extendoffice.com )

La formule qui suit permet dans la configuration suivante :
- on ne peut pas connaître à l'avance la taille de la chaîne de caractère qui sera utilisée
- on a besoin d'inclure un caractère dans cette chaîne à une place précise
- le fait d'y ajouter un caractère ne doit pas changer le nombre de caractères initial donc il faudra retirer le dernier caractère
- la chaîne de caractère créée doit être considérée comme un nombre

Afin de donner un exemple :
la chaîne de base est 205000000000

grâce à la formule j'obtiens : 280500000000

Mes deux chaîne feront toujours 12...

vgendron

XLDnaute Barbatruc
Bonjour

tu commences ton post par "J'ai créé un fichier"
et moi. tout ce que je vois, c'est un pavé.. du code en vrac des images.. et pas de fichier..

du coup. ca donne pas vraiment envie de lire..
je te conseille de poster ton fichier (anonymisé)
 

VincentH.DR

XLDnaute Nouveau
Bonjour

tu commences ton post par "J'ai créé un fichier"
et moi. tout ce que je vois, c'est un pavé.. du code en vrac des images.. et pas de fichier..

du coup. ca donne pas vraiment envie de lire..
je te conseille de poster ton fichier (anony
Bonsoir, merci pour le retour,

Je n'ai pas posté le fichier, car il contient des informations financières sur une entreprises et je ne souhaitais pas les divulguer, mais je fais le nécessaire, et je te joins le fichier.

Merci.
 

vgendron

XLDnaute Barbatruc
bon bah.. euh.. je ne comprend rien à ton fichier...

je ne vois pas la différence entre ce que tu obtiens et ce que tu souhaites avoir.. ni pourquoi comment..

avec ton fichier d'origine.. je suis prêt à parier que l'execution des différentes macros est assez lent...
faire une boucle sur 10000 lignes alors qu'il n'y en a que 25..
je pense que tu gagnerais à présenter les données sous forme de table structurée
==> plus facile à traiter dans le code
==> les formules et Mises en formes se recopient automatiquement à chaque nouvelle ligne
==> plutot qu'un if left(cell,2)=21 or...., on pourrait appliquer un filtre élaborer et copier coller directement le résultat du filtre..
 

VincentH.DR

XLDnaute Nouveau
bon bah.. euh.. je ne comprend rien à ton fichier...

je ne vois pas la différence entre ce que tu obtiens et ce que tu souhaites avoir.. ni pourquoi comment..

avec ton fichier d'origine.. je suis prêt à parier que l'execution des différentes macros est assez lent...
faire une boucle sur 10000 lignes alors qu'il n'y en a que 25..
je pense que tu gagnerais à présenter les données sous forme de table structurée
==> plus facile à traiter dans le code
==> les formules et Mises en formes se recopient automatiquement à chaque nouvelle ligne
==> plutot qu'un if left(cell,2)=21 or...., on pourrait appliquer un filtre élaborer et copier coller directement le résultat du filtre...
C'est gentil d'avoir regardé,

je vais tenter de continuer de chercher de mon côté pour essayer de trouver le code qui convient.

Merci encore.
 

VincentH.DR

XLDnaute Nouveau
Bonjour,

J'ai fini par trouver la solution à travers une formule et aussi grâce à un autre site qui avait une partie de la solution ( https://fr.extendoffice.com )

La formule qui suit permet dans la configuration suivante :
- on ne peut pas connaître à l'avance la taille de la chaîne de caractère qui sera utilisée
- on a besoin d'inclure un caractère dans cette chaîne à une place précise
- le fait d'y ajouter un caractère ne doit pas changer le nombre de caractères initial donc il faudra retirer le dernier caractère
- la chaîne de caractère créée doit être considérée comme un nombre

Afin de donner un exemple :
la chaîne de base est 205000000000

grâce à la formule j'obtiens : 280500000000

Mes deux chaîne feront toujours 12 caractères pour les besoins de la RECHERCHEV( ) qui est ensuite utilisée

CNUM(CONCAT(28;+STXT(GAUCHE(B9;NBCAR(B9)-1);2;NBCAR(GAUCHE(B9;NBCAR(B9))))))

En espérant que cela puisse être utile à une autre personne que moi,

Bonne journée à tous.
 

Discussions similaires

Réponses
1
Affichages
248
Réponses
0
Affichages
219
Réponses
9
Affichages
139

Statistiques des forums

Discussions
313 198
Messages
2 096 142
Membres
106 504
dernier inscrit
ngomez