VBA - macro calculs

Bonjour à tous,

Je possède une macro qui me permet d'inscrire des résultats de SOMME.SI.ENS au sein de certaines cellules choisies via un condition IF. Le souci que j'ai, est qu'elle est lente dans son exécution, lors que je la fais tourner sur 12000 lignes (il n'y a pas autant de lignes sur mon fichier exemple)

Je vous explique comment fonctionne mon fichier:

J'ai une liste de comptes en colonne B, à chaque ligne correspond un compte. Chaque ligne, donc chaque compte, a une cellule jaune ou verte en colonne J. Verte, signifie que c'est une cellule "totalisatrice", c'est-à-dire qu'elle fait la somme d'autres cellules. Ce sont ces cellules qui sont affectées par ma macro, car c'est dans celles-ci que cette dernière vient inscrire le résultat d'un SOMME.SI.ENS fonction de plusieurs critères. Donc toutes les cellules vertes de la colonne J sont destinées à être remplies par ma macro, à l'exception de celles qui sur leur ligne en colonne A, ont "Add ICP".

À partir de là, je pense que vous comprenez mieux le pourquoi de cette macro: elle parcourt l'ensemble de la colonne J et vérifie à chaque fois deux conditions:

If Cel1.Interior.Color = RGB(160, 203, 183) = True And Feuille.Range("A" & i) <> "Add ICP" Then

Si les conditions sont respectées, alors la macro remplit la cellule avec le résultat d'un SOMME.SI.ENS. Puis ensuite la macro fait la même chose en colonne K, puis en colonne L, etc...

Ce qui pourrait me faire gagner du temps je pense, est de réaliser le IF uniquement sur les cellules de la colonne J. Car à partir du moment où les deux conditions sont respectées en colonne J, elles le sont sur toutes les colonnes qui suivent jusqu'à la colonne V. Jusqu'ici ce IF est répété sur l'ensemble des cellules de chaque colonne, alors qu'à priori le faire une fois par ligne (dans la colonne J), suffirait!

Ai-je été clair ? Je vous remercie pour votre aide. J'ai pu discuté avec LouReeD sur le forum, et il semble me recommander l'utilisation de tableaux pour accélérer ma macro et éviter les multiples Range qui me renvoient vers Excel à chaque fois.. Je ne maîtrise absolument pas ces objets dans VBA, je me suis penché plusieurs fois sur de la doc en ligne, mais en pratique je trouve ça difficile. Même si j'ai conscience de la puissance de l'outil.

Bonne journée,

SkillzZ

20fichier-exemple.xlsm (189.88 Ko)

Bonjour,

On ne s'est pas déjà croisé sur un sujet similaire cette année?

Sur ce sujet: https://forum.excel-pratique.com/viewtopic.php?f=2&t=119742&p=729720#p729720

En quoi ta demande actuelle diffère de celle que tu avais fait?

Bonjour AuSecour,

Si effectivement et la demande est effectivement très similaire. Mais malgré tous les commentaires, je n'arrive pas m'approprier la macro, à vraiment comprendre son fonctionnement au point de pouvoir le refaire.

Ici, avant même l'utilisation de tableaux qui est une possibilité, je souhaitais réaliser la condition If uniquement sur la première colonne. C'était la première étape et ça me permettrait je pense de gagner du temps. L'idéal ensuite serait sûrement de fonctionner avec des tableaux comme tu l'as fait mais j'avoue ne pas avoir réussi à comprendre ton code. Je trouve la chose hyper abstraite, et pourtant j'ai lu tes commentaires à plusieurs reprises..

SkillzZ

C'est un peu normal que ce soit abstrait je pense, c'est du codage

Le truc c'est que c'était compliqué à programmer et si tu n'y connais pas grand chose en VBA tu peux vite être largué...

Mais du coup c'est du travail perdu en fait

En tout cas pour ton cas, il suffit en effet de ne vérifier qu'une fois, quand à la formule, fais une boucle sur toutes les colonnes où tu affecteras ta valeur.

Et passe par des tableaux si tu veux accélérer le code au lieu d'utiliser tous le temps les cellules, ça rend le code plus abstrait mais l'accélère grandement.

Non ce n'est pas perdu car j'ai gardé le code sous la main avec l'espoir de pouvoir bien le comprendre un jour. Donc effectivement je ne l'utilise pas aujourd'hui au sein de mon fichier mais j'ai travaillé dessus et le conserve donc tu n'as pas fait ça pour rien !

Il y a des codes plus abstraits que d'autres haha, car j'ai du mal avec certains, et moins avec d'autres!

Je vais réessayer de faire la vérification sur la première colonne uniquement, en espérant réussir cette fois ci..

Merci !

SkillzZ

Rebonjour Ausecour,

Je continue de réfléchir sur la macro que tu m'avais fourni en me disant que c'est vraiment ce type de solution dont j'ai besoin, bien que je ne maîtrise pas les tableaux. C'est pourquoi j'aimerais savoir s'il te serait possible de me proposer la même chose mais travaillant non pas uniquement sur la colonne J de mon fichier exemple, mais sur les colonnes J à V puis X à AI ? Je ne parviens pas à adapter ce que tu m'as proposé sur l'autre topic.. Mais ce serait vraiment génial. Même si je ne le comprends qu'à 60%, c'est la solution vers laquelle je dois me tourner, elle me permet d'économiser trop de temps pour que je puisse m'en passer. Accepterais-tu donc de me proposer la même chose à une échelle plus grande ?

Pour rappel, le code que tu m'avais transmis:

Sub test()
Dim ColecLig As New Collection 'on déclare un objet ColecLig qui va être un objet de type collection
    'une collection est un objet particulier, l'avantage par rapport à un tableau, c'est qu'il est redimensionnable
    'très facilement, il suffit d'utiliser additem ou removeitem pour ajouter ou supprimer un élément,
    'en revanche, elle ne contient qu'une colonne de données, l'avantage aussi c'est de pouvoir appeler un élément
    'via une clé, ici j'utilise le code de compte comme clé, la collection enregistre la ligne correspondante de ce code de
    'compte dans le tableau qui s'appelle tableau. La collection sert donc à faire un lien rapide entre un code et la
    'ligne du tableau où se trouve ce code, sans avoir à faire une boucle sur le tableau pour trouver le code
    'je fais souvent ça car je pense que ça va plus vite

Dim TabEnfants As Variant, TabSplit As Variant
der_lig = Range("b" & Rows.Count).End(xlUp).Row
tableau = Range("a6", "z" & der_lig)
ReDim TabEnfants(LBound(tableau, 1) To UBound(tableau, 1), 1 To 2) 'codes enfants + somme faite ou non
    'redim sert à redimensionner un tableau, ensuite on lui précise les dimensions que l'on va vouloir,
    'cette instruction est très pratique quand on ne connaît pas à l'avance la taille du tableau et que l'on veut
    'y mettre des variables

'création de la collection de lignes
On Error Resume Next 'permet de passer à la ligne suivante en cas d'erreur, j'ai dû mettre ça car tu avais
    'des doublons au niveau de tes codes de compte, mais je ne pouvais pas ajouter une clé identique pour deux valeurs
    'si tu as besoin de traiter les doublons, j'ajouterai un numéro de ligne à la clé pour pouvoir avoir des valeurs
    'uniques, mais comme je ne comprenais pas pourquoi tu avais des doublons, je me suis abstenu
For i = LBound(tableau, 1) To UBound(tableau, 1)
    'lbound permet de renvoyer la borne inférieure de la dimension d'un tableau,
    'dit comme ça ça ressemble à du chinois, un tableau a en général deux dimensions,
    'les lignes, et les colonnes, ligne = 1, colonnes = 2
    'les tableaux ont un numéro d'index, en fonction de comment tu les déclares, le premier élément d'un tableau
    'peut se retrouver à l'index 1 voir 0, j'utilise lbound pour ne pas me prendre la tête
    'lbound(tableau,1) = borne inférieure de la première dimension du tableau qui s'appelle "tableau"
    ColecLig.Add i, tableau(i, 2) 'ajoute à la collection "ColecLig" la valeur i, avec la clé tableau(i,2) (ligne i, colonne 2)
Next i

'Enregistrement des enfants de chaque compte
For i = LBound(tableau, 1) To UBound(tableau, 1)
    'ColecLig(tableau(i, 26)) peut se traduire par "ligne où se trouve ce code dans le tableau enfant"
    'iif = un SI, tu fais un test, puis tu mets valeur si vrai, et valeur si faux
    'ici ça sert à ajouter une virgule ou non pour séparer les codes enfant si il y a en a déjà un de stocké,
    'comme ça: "enfant1,enfant2"
    TabEnfants(ColecLig(tableau(i, 26)), 1) = TabEnfants(ColecLig(tableau(i, 26)), 1) & _
    IIf(TabEnfants(ColecLig(tableau(i, 26)), 1) <> "", ",", "") & tableau(i, 2)
Next i
On Error GoTo 0 'permet de remettre le paramètre de base en cas d'erreur, c'est à dire, afficher un message d'erreur
    'et faire planter le code

'Calcul des sommes
'pour toutes les lignes du tableau
For i = LBound(tableau, 1) To UBound(tableau, 1)
    'je mets les enfants du code dans une variable pour pouvoir l'utiliser dans le sous programme "somme:"
    'j'ai dû faire ça car dans le sous programme j'utilise aussi les enfants, mais d'un enfant,
    'c'est l'enfant de l'enfant qui peut aussi avoir des enfants qui ont des enfants...
    'sauf que dans un cas j'utilise la variable i pour avoir la ligne, et dans l'autre, h, je devais avoir
    'une variable pour stocker l'une des deux valeurs, puis ensuite me permettre d'avoir exactement le même code
    'sinon j'aurais dû avoir deux fois le même code avec juste une variable qui change...
    enfants = TabEnfants(i, 1)

    If enfants <> "" Then 'je teste si il a des enfants avant de vouloir faire la somme de ses enfants
        tableau(i, 10) = 0 'je réinitialise la valeur du total
        GoSub somme 'je file dans le sous programme
        TabEnfants(i, 2) = "fait" 'une fois la somme de ses enfants faite, je passe le statut de cette somme
            'en fait, pour ne pas recalculer cette somme si je retombe sur ce code de compte
    End If
Next i

'ici c'est la dernière ligne lue, elle permet d'exporter le tableau où on a tout modifié dans le fichier
Range("a6", "z" & der_lig) = tableau
Exit Sub 'permet de terminer le programme

somme: 'c'est la façon dont on écrit une ligne accessible via goto ou gosub, la différence c'est que quand on appelle
    'cette ligne avec un gosub, on a un return plus tard pour revenir à la ligne qui a appelé le sous programme

    'enregistrement des codes comptes des enfants dans un tableau
    TabSplit = Split(enfants, ",") 'split est une fonction qui retourne un tableau,
        'ce tableau est le résultat de la séparation de plusieurs valeurs via un délimiteur,
        'ici on stockait les différents codes enfants séparés par une virgule, du coup j'utilise "," pour séparer
        'les valeurs
    For h = LBound(TabSplit, 1) To UBound(TabSplit, 1) 'on boucle sur tous les enfants du code
        If TabEnfants(ColecLig(TabSplit(h)), 2) = "" Then 'si la somme des enfants de ce code n'a pas été faite
            enfants = TabEnfants(ColecLig(TabSplit(h)), 1) 'on enregistre dans la variable enfant les enfants...
            If enfants <> "" Then 'on regarde si le code compte a des enfants
                GoSub somme 'si c'est le cas on appelle à nouveau le sous programme somme
            End If
        End If
        tableau(i, 10) = tableau(i, 10) + tableau(ColecLig(TabSplit(h)), 10) 'c'est ici qu'on ajoute progressivement
            'les valeurs pour faire la somme
    Next h
Return 'on revient à la ligne qui a appelé le sous programme
End Sub

Par ailleurs tu m'avais demandé si je devais traiter les doublons au sein de la colonne B. Oui je dois les traiter. Car en fait les sommes réalisées via les comptes "fils" doivent se faire uniquement pour les fils qui ont le même C2 Code (colonne D) que leur parent. Donc par exemple, le compte PL50000T peut apparaître plusieurs fois, mais sous des C2 Code différents. Et leur somme vont se différencier par le fait que leur compte fils seront additionnés uniquement s'ils ont le même C2 Code.

Je te souhaite une bonne journée, et j'espère à bientôt,

SkillzZ

Re,

Normalement j'ai correctement modifié le programme, mais je ne pouvais pas le tester car tu n'as aucune colonne qui contient le code du compte parent, j'ai plein de valeur erreur en fin de fichier (tout à droite).

J'ai supposé que c'était la colonne AZ qui contenait les codes mais bon, pas bien sûr...

Du coup j'ai modifié les valeurs 26 qui faisaient référence avant à la colonne Z par une variable, je lui ai mis la valeur 52 en début de programme.

Voici le fichier en espérant que ça fonctionnera...

PS: j'ai viré tous les commentaires que j'avais ajouté car ça faisait du volume pour rien, et moi ça me gênait plus qu'autre chose...

Merci AuSecour pour ta réponse et ta macro.

Je l'ai testée et je crois qu'elle fonctionne qu'à moitié. En effet, elle ne fait pas les sommes totalisatrices à la ligne 22 par exemple.

Par ailleurs, j'aimerais que les sommes se fassent dans les colonnes J à V mais aussi X à AI. Jusqu'ici je pense qu'elles se font uniquement de J à V.

De plus, je ne sais pas si tu as vu cette spécificité dans ma demande, mais je veux prendre les doublons en compte, car ils se différencient par leur C2 Code. Je veux à chaque fois, sommer l'ensemble des fils qui ont le même C2 code que la ligne parent concernée.

Je ne sais pas si intégrer ces modifications dans ton code va te coûter cher en temps et en neurones, mais si c'est possible ce serait top !

Je t'ai retransmis le fichier avec les noms de compte parent en AZ

SkillzZ

Re

J'avais fait plusieurs erreurs, du coup... heureusement que j'ai eu le fichier pour tester par moi-même cette fois

En fait, l'initialisation de tableau se faisait jusqu'à Z, première erreur, deuxième erreur, j'exportais le tableau jusqu'à Z, j'ai mis AI aussi.

Autre chose, je n'initialisais que la colonne 10 des totaux du code de compte, c'est corrigé, j'initialise toutes les colonnes de totaux maintenant.

J'ai également modifié le code pour prendre en compte les codes C2 pour ma clé de recherche dans la collection ColecLig.

J'ai testé et ça semble fonctionner

Je te laisse voir le résultat:

Pour le coup, le calcul est instantané de mon côté

Merci AuSecour !!

Je teste ça dans 15min ! C'est super, merci beaucoup AuSecour!

Je te tiens au courant

Bonne journée !!

SkillzZ

Re

De rien

Je viens de voir ton message privé, ce n'était pas nécessaire de m'en envoyer un

Comme le programme est plutôt bien écrit (non non je ne prends pas la grosse tête ), il était plutôt simple à modifier, beaucoup plus simple à modifier qu'a créer en fait C'est dire!

Tu peux toujours poser des questions si tu bloques pour le sous programme somme Il y a une logique que même moi j'avais oublié quand j'ai vu ça

Re,

Ton travail est super AuSecour et presque parfait vis à vis de mon besoin ! Il y a deux petites choses que j'aimerais éviter dont une que je n'avais pas mentionnée, parce que je m'en suis rendu compte que maintenant..

1) J'aimerais que les lignes qui dont le nom de compte n'est pas en gras ne soient pas concernées par les calculs de la macro. Les lignes "enfants" en fait. Ce qui me surprend est que c'est le cas pour les colonnes J:U mais pas pour les colonnes V et X:AI. Par exemple, ta macro vient inscrire des résultats sur les lignes 6, 7, 8, 9 pour les colonnes X:AI par exemple, mais dans ces cellules il y a initialement des formules que je souhaiterais conserver. Je t'ai donc retransmis le fichier en identifiant en jaune les cellules (et donc les lignes) que j'aimerais impacter via la macro.

2) Toutes les lignes en gras ne sont pas jaunes car il y a quelques exceptions.. Et ces exceptions s'identifient facilement par le biais de la mention "Add ICP" en colonne A. Ces lignes exceptions vont aussi contenir des formules que je souhaiterais conserver.

Voilà les deux seules choses (que j'ai vues, et j'espère avoir bien vu ) qui m'ennuient par rapport à ce que je souhaite faire mais sinon c'est incroyable. Ta macro fait le travail en moins d'une seconde (il faut que je la teste sur 12000 lignes quand même mais je pense que ça restera raisonnable) alors que la mienne le fait en 20 minutes sur 12000 lignes sans agir sur les colonnes X:AI! C'est dire la puissance des tableaux!

Si tu pouvais intégrer ces deux petites modifications AuSecour, ce serait génial, vraiment!

Dans tous les cas je te remercie pour le travail accompli, c'est vraiment généreux de ta part et ça va me faciliter énormément les choses!

A+,

SkillzZ

EDIT: Désolé pour le double poste j'avais pas vu qu'on arrivait sur une seconde page, et je pensais que mon premier poste n'avait pas fonctionné..

Re,

Ton travail est super AuSecour et presque parfait vis à vis de mon besoin ! Il y a deux petites choses que j'aimerais éviter dont une que je n'avais pas mentionnée, parce que je m'en suis rendu compte que maintenant..

1) J'aimerais que les lignes qui dont le nom de compte n'est pas en gras ne soient pas concernées par les calculs de la macro. Les lignes "enfants" en fait. Ce qui me surprend est que c'est le cas pour les colonnes J:U mais pas pour les colonnes V et X:AI. Par exemple, ta macro vient inscrire des résultats sur les lignes 6, 7, 8, 9 pour les colonnes X:AI par exemple, mais dans ces cellules il y a initialement des formules que je souhaiterais conserver. Je t'ai donc retransmis le fichier en identifiant en jaune les cellules (et donc les lignes) que j'aimerais impacter via la macro.

2) Toutes les lignes en gras ne sont pas jaunes car il y a quelques exceptions.. Et ces exceptions s'identifient facilement par le biais de la mention "Add ICP" en colonne A. Ces lignes exceptions vont aussi contenir des formules que je souhaiterais conserver.

Voilà les deux seules choses (que j'ai vues, et j'espère avoir bien vu ) qui m'ennuient par rapport à ce que je souhaite faire mais sinon c'est incroyable. Ta macro fait le travail en moins d'une seconde (il faut que je la teste sur 12000 lignes quand même mais je pense que ça restera raisonnable) alors que la mienne le fait en 20 minutes sur 12000 lignes sans agir sur les colonnes X:AI! C'est dire la puissance des tableaux!

Si tu pouvais intégrer ces deux petites modifications AuSecour, ce serait génial, vraiment!

Dans tous les cas je te remercie pour le travail accompli, c'est vraiment généreux de ta part et ça va me faciliter énormément les choses!

A+,

SkillzZ

Re!

En effet, j'écris également sur les formules, mais ce n'est pas un problème, j'ai déclaré un tableau supplémentaire chargé de stocker les formules des cellules (ça ira plus vite que d'appeler la propriété hasformula de chaque cellule), si la formule est du texte, on ne remplace pas son contenu, sinon on le remplace par la valeur du tableau de calcul, et on exporte ensuite le tableau qui contient les formules (qui peut t'ouvrir l'explorateur de fichier si il ne trouve pas un fichier auquel fait référence une formule).

Il faudra que tu fasses fonctionner la macro sur un fichier où elle n'a pas déjà tourné par contre, sinon tu n'auras plus de formules sur ces cellules

En tout cas j'ai testé sur un fichier où il en restait et elles n'ont pas été écrasées

Je te laisse vérifier sur un fichier où aucune macro n'a tournée

Re AuSecour !

J'ai eu peur que tu ne répondes plus, merci beaucoup pour ta réponse, c'est super !! J'ai 3 remarques/questions à son sujet:

- Effectivement on n'écrase plus les formules et ça c'est top ! En revanche je ne suis pas sûr d'avoir compris pourquoi une fenêtre s'ouvrait lorsque je lance la macro. Si j'ai bien compris, tu me dis que l'explorateur de fichier s'ouvre si jamais la macro tombe sur une formule qui fait référence à un fichier qu'elle ne trouve pas ? C'est ça? Si oui, c'est uniquement pour les formules situées dans les cellules ou la macro effectue des calculs?

Est ce que c'est qqchose d'évitable le fait que l'explorateur s'ouvre (car il s'ouvre effectivement)?

- Pour résumer et m'assurer que j'ai bien saisi le fonctionnement de la macro: elle réalise la même chose que précédemment, à l'exception que si une cellule contient une formule, alors elle ne l'affecte pas ? Si c'est effectivement le cas, ça répond à ma problématique car ce que je disais à propos des "Add ICP" est réglé de la même manière que les formules de mon 1) et c'est super!

- Enfin, j'ai essayé de faire tourner la macro sur mon fichier original et j'ai une "erreur de type 9: l'indice n'appartient pas à la sélection" au niveau de la ligne suivante:

lig = ColecLig(TabSplit(h))

Comme tu t'en doutes je ne peux pas transmettre mon fichier original mais peu de choses diffèrent hormis le fait qu'il s'étend sur beaucoup plus de lignes. Les tableaux ont-ils une limite de stockage?

Je te remercie encore AuSecour, tu m'aides beaucoup !

SkillzZ

*Autre petite chose dont je viens de me rendre compte: si les cellules affectées par la macro sont vides (ni texte, ni formule), je vois que la macro ne renseigne rien alors que j'aimerais qu'elle fasse les calculs à partir du moment ou il n'y a pas de formule. Est-ce possible assez facilement?

Merci,

SkillzZ

- Enfin, j'ai essayé de faire tourner la macro sur mon fichier original et j'ai une "erreur de type 9: l'indice n'appartient pas à la sélection" au niveau de la ligne suivante:

lig = ColecLig(TabSplit(h))

Comme tu t'en doutes je ne peux pas transmettre mon fichier original mais peu de choses diffèrent hormis le fait qu'il s'étend sur beaucoup plus de lignes. Les tableaux ont-ils une limite de stockage?

Je pense que c'est un problème du à mes données et non pas le travail que tu as fait. Par exemple, un enfant dont la parent n'est pas retrouvé, en colonne B, ça pourrait faire ce genre d'erreur ? Car j'ai ce cas là dans mon fichier global..

Mon étude du problème continue et avec un point d'arrêt et du "F8", je me rends compte qu'il se passe quelque chose d'étonnant: à un moment donné, lorsque j'arrive au "Return" de la fin, celui-ci ne me renvoie pas vers le premier GoSub somme mais vers le second:

If enfants <> "" Then
    GoSub somme
End If

Je pense que ce n'est pas normal mais peut-être que je me trompe. Ce qui est sûr, c'est qu'ensuite en arrivant sur:

lig = ColecLig(TabSplit(h))

TabSplit(h) génère l'erreur de type 9 que j'ai évoqué plus haut..

Bonjour,

Tu as énormément écris, je vais quand même essayer de répondre à tout:

J'ai eu peur que tu ne répondes plus

Il faut me laisser le temps de répondre, si tu commences à m'écrire au bout de 4 heures sans réponse, je lâche l'affaire...


Effectivement on n'écrase plus les formules et ça c'est top ! En revanche je ne suis pas sûr d'avoir compris pourquoi une fenêtre s'ouvrait lorsque je lance la macro. Si j'ai bien compris, tu me dis que l'explorateur de fichier s'ouvre si jamais la macro tombe sur une formule qui fait référence à un fichier qu'elle ne trouve pas ? C'est ça? Si oui, c'est uniquement pour les formules situées dans les cellules ou la macro effectue des calculs?

En fait, dès que la valeur contenue par le tableau TabFormules n'est pas un nombre, on ne remplace pas sa valeur, donc on ne touche pas non plus aux formules qui ne sont pas des nombres. Oui on peut ne pas afficher l'explorateur de fichiers, j'ai juste rajouté une commande pour désactiver les alertes le temps d'exporter le tableau, l'explorateur de fichiers ne s'ouvre plus après cette action.


- Enfin, j'ai essayé de faire tourner la macro sur mon fichier original et j'ai une "erreur de type 9: l'indice n'appartient pas à la sélection" au niveau de la ligne suivante:

lig = ColecLig(TabSplit(h))

C'est bizarre que tu aies une erreur ici, ça veut dire qu'on est tombé sur un enfant, avec un code de compte, un code C2, qui a été ajouté aux enfants du parent dans le tableau TabEnfants, ce qui est totalement sûr, puis... En voulant appeler à nouveau l'enfant, il ne le trouve pas, c'est super étrange, ça ne devrait pas arriver, et ça n'arrivait pas dans le fichier que tu m'avais fournis...


Comme tu t'en doutes je ne peux pas transmettre mon fichier original mais peu de choses diffèrent hormis le fait qu'il s'étend sur beaucoup plus de lignes. Les tableaux ont-ils une limite de stockage?

Si tu avais atteins une limite de mémoire, tu aurais eu un message du type "Mémoire insuffisante", je ne pense pas que ce soit ça du coup.


Autre petite chose dont je viens de me rendre compte: si les cellules affectées par la macro sont vides (ni texte, ni formule), je vois que la macro ne renseigne rien alors que j'aimerais qu'elle fasse les calculs à partir du moment ou il n'y a pas de formule. Est-ce possible assez facilement?

Je pense que isnumeric devait renvoyer faux si la cellule était vide, comme le tableau TabFormules contient du texte, au lieu d'être égal à 0, ça donne "" qui est du texte, j'ai juste rajouté un Or pour ajouter les cas où la formule est "".


Je pense que c'est un problème du à mes données et non pas le travail que tu as fait. Par exemple, un enfant dont la parent n'est pas retrouvé, en colonne B, ça pourrait faire ce genre d'erreur ? Car j'ai ce cas là dans mon fichier global..

Heureusement, on peut gérer les erreurs sous VBA, il suffit de dire avant l'affectation de la ligne, si il y a une erreur, on passe à telle ligne.


Mon étude du problème continue et avec un point d'arrêt et du "F8", je me rends compte qu'il se passe quelque chose d'étonnant: à un moment donné, lorsque j'arrive au "Return" de la fin, celui-ci ne me renvoie pas vers le premier GoSub somme mais vers le second

C'est voulu, le return renvoie vers la dernière ligne qui a appelé le sous programme "somme:", dans ton cas, c'est ici:

If enfants <> "" Then
    GoSub somme
End If

La logique derrière tout ça, c'est que ta ligne de total peut faire appel à une cellule qui est également une ligne de total, je teste alors si cette cellule a déjà fait le total des valeurs de ces enfants, si ce n'est pas le cas, je ne peux pas directement utiliser la valeur de ce total, je fais donc le total à sa place.

Je te passe le code corrigé:

Spoiler
Sub test()
Dim ColecLig As New Collection

Dim TabEnfants As Variant, TabSplit As Variant, TabFormules As Variant
Dim ColParent, ColAccount, ColC2Code
der_lig = Range("b" & Rows.Count).End(xlUp).Row
tableau = Range("a6", "az" & der_lig)
TabFormules = Range("a6", "az" & der_lig).Formula
ColParent = 52
ColAccount = 2
ColC2Code = 4
ReDim TabEnfants(LBound(tableau, 1) To UBound(tableau, 1), 1 To 2)

'création de la collection de lignes
On Error Resume Next
For i = LBound(tableau, 1) To UBound(tableau, 1)
    If tableau(i, ColAccount) <> "" Then
        ColecLig.Add i, tableau(i, ColAccount) & "_" & tableau(i, ColC2Code)
    End If
Next i

'Enregistrement des enfants de chaque compte
For i = LBound(tableau, 1) To UBound(tableau, 1)
    clé = tableau(i, ColParent) & "_" & tableau(i, ColC2Code)
    TabEnfants(ColecLig(clé), 1) = TabEnfants(ColecLig(clé), 1) & _
    IIf(TabEnfants(ColecLig(clé), 1) <> "", ",", "") & tableau(i, ColAccount) & "_" & tableau(i, ColC2Code)
Next i
On Error GoTo 0

'Calcul des sommes
For i = LBound(tableau, 1) To UBound(tableau, 1)
    enfants = TabEnfants(i, 1)

    If enfants <> "" Then
        'Effacement des valeurs de somme précédentes
        For j = 10 To 22
            tableau(i, j) = 0
        Next j
        For j = 24 To 35
            tableau(i, j) = 0
        Next j
        GoSub somme
        TabEnfants(i, 2) = "fait"
    End If
Next i

'Injection des valeurs chiffrées sur les cases qui ne contiennent pas une formule
For i = LBound(TabFormules, 1) To UBound(TabFormules, 1)
    For j = 10 To 22
        If IsNumeric(TabFormules(i, j)) Or TabFormules(i, j) = "" Then
            TabFormules(i, j) = tableau(i, j)
        End If
    Next j
    For j = 24 To 35
        If IsNumeric(TabFormules(i, j)) Or TabFormules(i, j) = "" Then
            TabFormules(i, j) = tableau(i, j)
        End If
    Next j
Next i

'Exportation du tableau
Application.DisplayAlerts = False
Range("a6", "ai" & der_lig).Formula = TabFormules
Application.DisplayAlerts = True
Exit Sub

somme:
    'enregistrement des codes comptes des enfants dans un tableau
    TabSplit = Split(enfants, ",")
    For h = LBound(TabSplit, 1) To UBound(TabSplit, 1)
        If TabEnfants(ColecLig(TabSplit(h)), 2) = "" Then
            enfants = TabEnfants(ColecLig(TabSplit(h)), 1)
            If enfants <> "" Then
                GoSub somme
            End If
        End If
        On Error GoTo suite
        lig = ColecLig(TabSplit(h))
        For j = 10 To 22
            tableau(i, j) = tableau(i, j) + IIf(IsNumeric(tableau(lig, j)), tableau(lig, j), 0)
        Next j
        For j = 24 To 35
            tableau(i, j) = tableau(i, j) + IIf(IsNumeric(tableau(lig, j)), tableau(lig, j), 0)
        Next j

suite:
        On Error GoTo 0
    Next h
Return
End Sub

Salut AuSecour,

Merci pour cet n-ième retour. C'est super, je vais tester ça dans l'après-midi dès que possible. Merci beaucoup pour toutes les réponses apportées à chacune de mes questions !

SkillzZ

Rechercher des sujets similaires à "vba macro calculs"