Comment rendre plus fluide le fait de tirer une formule ?

Bonjour à tous,

Après mon premier post qui a été résolu hyper rapidement, je m'en permet un second. Cette fois-ci aucun problème, plus une discussion générale :

Dans plusieurs fichiers je tire une formule vers le bas via une macro classique type :

DerLigneFormule = Cells(Rows.Count, 1).End(xlUp).Row 'Permet de compter la dernière ligne de A

Range("Y2").AutoFill Destination:=Range("Y2:Y" & DerLigneFormule)

Sauf que voila quand je tire plusieurs formules qui sont "gourmandes", l'exécution de la macro est plutôt longue et souvent le fichier se grise et se bloque pendant un moment. Une idée pour éviter cela ? surtout quand on a plusieurs milliers de lignes ..

J'ai déjà essayé de decomposer la macro en plusieurs étapes mais c'est pas hyper concluant. Mettre des pauses dans la macro aiderait selon vous ?

Merci à tous pour votre aide / participation à cette question

Bonjour,

ton souci vient sûrement de la méthode de calcul de ton classeur, quand tu glisses ces formules, elles veulent se calculer automatiquement. Soit tu allèges les formules, soit tu passes la méthode de calcul en manuel, en VBA ça donne:

Application.Calculation= xlCalculationManual

Oui j'ai tenté cela mais un truc très bizarre se produit.

-> Pour des formules aucun problème ça se tire en instantanée pour d'autres c'est très très long alors que je suis bien en mode manuel. Ce sont des formules "complexes" (un somme.si.ens mais sur 20 000 lignes ça doit etre lourd). Et je vois bien qu'elle ont bien été tirées en mode manuel. La phase de calcul derrière est très longue aussi donc ..

Formule type : (en AI2)

=SI(ET(AA2="oui";SOMME.SI.ENS(AI$1:AI1;B$1:B1;B2)=0);1;0)

j'imagine que c'est long même à tirer en mode manuel parce que ma plage est mobile, la cellule en AI1000 étant :

=SI(ET(AA1000="oui";SOMME.SI.ENS(AI$1:AI999;B$1:B999;B1000)=0);1;0)

Je vais regarder pour mettre directement le résultat de la formule et non la formule en elle même mais je ne sais pas si cela est possible.

Hum en effet c'est bizarre...

Sinon ce n'est pas possible de reproduire ce que fait la formule mais en VBA? Dans le cas de la formule que tu as mis, j'ai l'impression que tu veux mettre 1 à la première occurrence d'une valeur en colonne B si en colonne A tu as "oui". Avec un objet type Dictionary (ou Dictionnary je ne sais plus), c'est possible de le faire assez rapidement je pense. C'est peut-être à cause des plages dynamiques comme tu le dis, je dois avouer ne pas avoir suffisamment travaillé avec ce type de plages pour savoir si c'est causé par ça...

Après j'imagine que ça doit être ça comme les formules ne sont pas calculées, il faut quand même les générer... Avec un

Application.ScreenUpdating = False

au tout début de ton code, ça va plus vite? Je doute que ça fasse grand chose mais je mets souvent cette ligne dans les miens, ça permet d'aller plus vite.

Alors bizarre mais ça suit la logique d'excel :

j'ai crée un nouveau fichier avec mode de calcul manuel et tirer des formules à la main sans aucune macro, c'est bien plus long avec les formules ayant des plages dynamiques que les autres...

Je suis bien Application.ScreenUpdating = False :/

Pour l'instant c'est degeu mais je vais laisser des formules jusqu'a la ligne 50 000 comme ça je suis large et je recalcule juste mes formules. Ca en fait plus à recalculer mais j'y gagne quand même du temps .. Je vais regarder ta proposition par contre, ça peut carrément m'aider !

Bonjour messieurs,

@Simon_D perso, je ne ferais pas un remplissage vers le bas, mais une inscription directe dans toute la plage

DLigF = Cells(Rows.Count, 1).End(xlUp).Row 'Permet de compter la dernière ligne de A
Range("AI2:AI" & DLigF).FormulaLocal = "=SI(ET(AA2=""oui"";SOMME.SI.ENS(AI$1:AI1;B$1:B1;B2)=0);1;0)"

A essayer

J'ai essayé ! Je me suis rendu compte après que je pouvais passer comme ça ! (il y a 5 minutes ...)

With wb.Worksheets("Base")
        .Range("AH2:AH" & DerLigneFormule).FormulaR1C1 = "=IF(AND(RC[-9]=""oui"",SUMIFS(R1C:R[-1]C,R1C[-32]:R[-1]C[-32],RC[-32])=0),1,0)"
        .Range("AI2:AI" & DerLigneFormule).FormulaR1C1 = "=IF(AND(RC[-8]=""oui"",SUMIFS(R1C:R[-1]C,R1C[-33]:R[-1]C[-33],RC[-33])=0),1,0)"

End With

Par contre ça n'a pas l'air de changer grand chose :/ Mais dans tous les cas c'est plus propre et ça fait moins de lignes !

Par contre quelle est la différence entre .Formula / . FormulaLocal / FormulaR1C1 ?

Re,

Toujours perso, je préfère "FormulaLocal" car on peut écrire les formules comme ce qu'il y a dans les cellules (ou presque)

A+

Bonjour à tous>

Des formules sur 20 000 lignes : il est normal que le temps de calcul soit long, notamment ce type de formules