Calcul automatique Résolu

Question

Bonjour,

En fait, pour vous expliquer, dans la feuil1 de mon classeur, j'ai un tableau de 3000 lignes de A à AN.
Vous comprendrez donc que lorsque je met le calcul automatique ça rame pas mal à chaque ouverture.

J'ai des formules dans les colonnes AB à AN.

Je voudrais savoir si c'est possible qu'à chaque ouverture de mon fichier excel , il y ait un calcul automatique des formules dans ces colonnes; et une fois le calcul fait, le calcul automatique soit déconnecté.

Merci de votre aide

greg · Answer

Bonjour,
ca doit certainement exister ... mais faire 39000 calculs dans une feuille Excel c'est de la folie, apprend un petit langage de programmation et tout deviendra si simple et si rapide ... :-)
A++

tchulio · Answer

Je ne sais pas comment faire pour la programmation des calcul. je connait un peut pour les USF mais pas plus.

Pour finir la plage de calcul est de la colonne "Z" à la colonne "AM"

Si personne ne peut me dire pour le calcul automatique, est-ce que quelqu'un pourrais m'aider pour le code de programmation ? ce fichier est vraiment important pour moi...

le voici en version allégée:

http://www.cijoint.fr/cjlink.php?file=cj200809/cijy5JT1X3.xls 

Merci beaucoup pour votre aide....

tchulio · Answer

;

tchulio · Answer

up

tchulio · Answer

personne pour m'aider alors...

Mike-31 · Answer

Salut,

On va assayer de faire quelque chose, que veux tu exactement faire comme calcul par colonne, le total de chaque colonne, ensuite supprimer la fonction somme.
Le plus simple est de mettre sur le forum un exemple avec des anotations que l'on comprenne, et ces calculs doivent se faire à l'ouverture de fichier ou chaque fois que tu changes d'onglet et en revenant sur ta feuille elle s'actualise, le code est différent

Pour mettre ton modèle, avec ce lien

https://www.cjoint.com/

A+

eriiic · Answer

Bonsoir,

tu peux aussi désactiver le calcul automatique et tu fais F9 quand tu dois rafraichir (avec tous les risque d'oublier de la même façon que si tu le désactives après l'ouverture...). Il faudrait ajouter un calculate à la fermeture du classeur
Tu peux également, quand tu as une très grande feuille, ne garder les formules QUE sur la 1ère ligne, et pour les autres tu fais copier/collage spécial valeur. Surtout qu'un grand nombre de colonnes ne doit plus bouger bcp... Et quand c'est necessaire tu as toujours ta formule dispo. Un simple double clic sur le carré noir de la cellule et elle est recopiée jusqu'en bas.
eric

Mike-31 · Answer

Salut Eriiic,

 Exact je suis passé un peu vite sur les posts, quand j'ai voulu rattraper le coup tu était déjà passé par là et donc bloqué la modif à mon post.

Bonne soirée

A+

tchulio · Answer

rebonjour;

Alors j'ai essayé de moultes façon afin de subvenir à mes besoins, mais je bloque totalement.

Voici ce que j'essaye de faire afin que ma liste ne rame plus: de la colonne Z à  AM j'ai des formule que j'ai jusqu'à la ligne 10000, du genre N° de semaine ... , ce que j'aimerais c'est que toutes les formule de ces colonnes se trouvent dans une macro, comme on me l'a conseillé, mais comment dois je faire; est ce que quelqu'un peu m'aider? 

voici une version très simplifiée du fichier:

http://www.cijoint.fr/cjlink.php?file=cj200810/cijW6WzTKk.xls

ceci est très important pour moi, pour mon travail... merci beacoup...

eriiic · Answer

Bonjour,

Précise un peu ta demande.

- Pourquoi désactiver le calcul automatique et le faire sur demande n'est pas suffisant ?
Si on te calcule tout par macro toute évolution te sera difficile si tu ne connais pas vba. On peut simplement l'obliger à calculer à la fermeture.

Si tu veux tjs calculer en vba :
- Quelle colonne peut-on considérée comme toujours remplie pour détecter le nombre de ligne à traiter ? F ?
- Peut-on utiliser AN pour marquer qu'une ligne a été calculée ? sinon laquelle en sachant que plus elle loin, plus le fichier sera gros.
- tu utilises no.semaine(). Cette fonction calcule selon le systeme américain et non européen (et il y a une différence la dernière semaine de l'année, voire même toute l'année pour certaines). Est-ce voulu ?

eric

tchulio · Answer

Car a chaque fois que j'utilise un filtre auto et qu'il y a le calcul automatique, le recalcul prend 5 min, c'est énorme..
En faite je me débrouille pas si mal en VBA, en faite de bonnes bases pour les USF... J'apprends gentillement.

Se sera plus simple et moins lourd pour mon fichier qu'il n'y ait plus de formules dans la feuille produit et que ceci soit dans la macro.

Oui la colonne toujours remplie, enfin celle qui se rempli obligatoirement à chaque fois qu'une ligne s'ajoute est la colonne F.

Oui pas de souci pour AN.

PAr contre pour no.semaine() je ne savais pas que s'était américain. Ceci n'est pas voulu que devrais je utiliser?

Et par ou dois je commencer pour ma macro ?

MErci beaucoup...

eriiic · Answer

ok, j'aurais pê un peu de temps ce soir pour regarder (ou mike...)
eric

tchulio · Answer

merci c'est vraiment cool...

eriiic · Answer

Bonjour,

voici un résultat partiel.
Je n'ai pas encore utilisé AN pour marquer les lignes calculées, je l'ajouterai en dernier.
nb.jours.ouvrés reste en formule pour l'instant car la fonction excel n'est pas récupérable en vba, je verrais plus tard.
Je n'ai pas fait AJ car tu fais référence à AF qui est un n°de semaine et non un nombre de jours. Si tu pouvais eclaircir ce point stp
http://www.cijoint.fr/cjlink.php?file=cj200810/cijPDEuCLR.xls
eric

eriiic · Answer

nouvelle version avec calcul des jours ouvrés.
Pour moi AA et AI sont équivalents. Tu confirmes ?
Au passage une fois calculé par macro on ne peut plus insérer/supprimer de colonnes....
http://www.cijoint.fr/cjlink.php?file=cj200810/cijjQaaMy4.xls
eric

eriiic · Answer

et encore une, ça avance...
j'avais oublié le calcul de AM, lorsque le filtre sur les lignes déjà calculées sera actif il faudra me faire penser que cette colonne sera à calculer systématiquement car fait référence à aujourdhui().
Oublie les autres versions mais réponds quand même aux questions précédentes.
http://www.cijoint.fr/cjlink.php?file=cj200810/cij6LyRKME.xls

tchulio · Answer

ouaha je vois que tu as bien avancé et je t'en remercie :) tu as l'air de vraiment maitriser...

Donc effectivement AA et AI sont identiques; erreur de ma part.

Et en fait pour AJ, encore une erreur de ma part. Normalement la référence est AI (Temps de passage)

Et se n'est pas grave pour l'insertion, suppression de colonnes.

J'ai regardé la macro, c'est vraiment balèze... PAr contre comment faire pour qu'elle soit active? Car quand j'ai rentré une date dans F, aucun effet.  Encore merci pour tout

eriiic · Answer

une version un peu plus finalisée, on attaque les finitions :
- gestion de la colonne AN, si tu veux tout recalculer supprimer les x de cette colonne
- ajout d'un raccourci clavier ctrl+r pour lancer la macro
- recalcul de la ligne si modif en F ou K (sauf lignes 1:2), tu pourras ajouter des colonnes en reprenant la partie
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row >= 3 And (Target.Column = 6 Or Target.Column = 11) Then
        'calculer la ligne
- je ne traite toujours pas la ligne 2 pour conserver les formules d'origine, il faudra modifier ici et en For i = 3 To Derlig

Teste un maximum et compare avec des feuilles déjà réalisées, pas question de revenir dans 15 jours en disant ah mais ça, ça ne marche pas, ou bien j'ai oublié ça.... ;-) J'aurais tout oublié de ta feuille d'ici là.
Teste aussi sur des grandes feuilles pour voir s'il faut essayer d'optimiser le temps de calcul.
Enfin bref, fais le maximum de contrôles
http://www.cijoint.fr/cjlink.php?file=cj200810/cijSPMFo7p.xls

tchulio · Answer

Re:

Ben tu as fais un sacré boulot là :)

Voici ce qui si passe sur mon pc, dès que je lance la macro ou que j'entre un date dans F ou K , j'ai ce message d'erreur qui apparait :

Erreur d'exécution '1004'

"ATPVBAEN.xla" introuvable. Vérifier l'ortographe du nom du classeur
et la validité de l'emplacement.
Si vous essayer d'ouvrir le fichier à partir de la liste des fichiers les plus
récents, assurez vous que le fichier n'a pas été renommé, déplacé ou 
supprimé.


Sais tu quelle peut être la cause de ce bug ? 

En tout cas très gros boulot que tu as fais...

eriiic · Answer

il faut que tu actives la macro 'utilitaires d'analyse' ou 'utilitaires d'analyse-VBA' (menu 'outils / macros complémentaires...' sur excel 2003), c'est pour la fonction nb.jours.ouvrés.

tchulio · Answer

Donc résultat des courses, tu es un as :)

j'ai essayé sur ma feuille qui a 5000 lignes a l'heure actuelle et ça va nickel, sauf la colonne AJ qui ne fonctionne pas est-ce normal ?

Et juste une question, etant donné que j'ai 5000 lignes dans mon tableau, je te laisse imaginer le nombre de formules a recalculer a chaque enregistrement. J'ai donc voulu effacer toutes les formules et faire pour que la macro fasse les calculs des lignes déjà entrées, mais  ça ne fonctionne pas, ou c'est peut etre moi qui m'y prend mal.

Merci encore pour tout

eriiic · Answer

effectivement il y avait un pb sur AI (dont dépend AJ), je continuais à le coller en AA, j'ai corrigé (pour rien, je l'ai mis en commentaires, voir suite).
Et comme AA est identique AI, maintenant je fais référence à AA pour AJ et je ne calcule plus AI (masquée), ça toujours ça de calculs inutiles en moins.

Si tu veux que la macro recalcule les lignes il faut penser à effacer les x sur les lignes concernées, je pense que c'est ça qui manquait.
Pour que tu y penses j'ai ajouté une message box 'calcul partiel ?' sur le ctrl+r, si tu réponds 'oui' (choix par défaut) ça ne calcule que les lignes non marquées (mais vu que le calcul se fait automatiquement sur une modif de date il ne devrait pas y en avoir bcp), si tu réponds 'non' ça efface tous les 'x' pour forcer un recalcul complet.

Comme apparement il n'y a pas trop d'erreur je traite maintenant également la ligne 2, si tu veux préserver tes formules qcq part penses-y...
http://www.cijoint.fr/cjlink.php?file=cj200810/cijrWWp671.xls

eric

tchulio · Answer

Bonjour, 

Donc j’ai essayé ce matin au travail, quand j’entre directement  sur la feuille la date dans F, ça fonctionne, par contre, quand je passe par ma macro (voir fichier joint) ça plante et me met se message d’erreur : 

Erreur d’exécution ‘13’ :
Incompabilité de type 

Sinon a part ça , ton programme est parfait. 


http://www.cijoint.fr/cjlink.php?file=cj200810/cij1r85TKe.xls

eriiic · Answer

Bonsoir,

Effectivement, c'est la fonction jours.ouvrés en vba qui retourne une erreur si les 2 dates sont égales.
remplacer:
    If Cells(i, 38) = 1 Then
        r = Application.Run("ATPVBAEN.XLA!networkdays", DateEnt, Date)
    Else
        r = ""
    End If
    Cells(i, 39) = rpar:
    If Cells(i, 38) = 1 Then
        If DateEnt = Date Then
            r = 1
        Else
            r = Application.Run("ATPVBAEN.XLA!networkdays", DateEnt, Date)
        End If
    Else
        r = ""
    End If    
    Cells(i, 39) = r
et pareil pour les autres....

Je force la valeur à 1 à moins que tu ne préfères vraiment que l'on teste le jour pour mettre 0 si la date entrée est un samedi ou un dimanche, du coup il y Z qui se rempli, je ne sais pas si c'est normal pour toi.
http://www.cijoint.fr/cjlink.php?file=cj200810/cijZ3T05Ht.xls

eric

tchulio · Answer

je ne comprend pas trop ce que tu veux dire; 

En fait il n'y aura pas de lignes rentrées le w.e , seulement du lundi au vendredi. Mais il se peut qu'une ou deux lignes soit entrées le samedi.

En fait si la date d'entrée et égale a la date de livraison et que jours.ouvrés =1 ça faussera mes tableau de stats.

j'essais 2-3 choses...

eriiic · Answer

re,

Je me suis basé excel (hors vba), fonction nb.jours.ouvrés(), qui retourne déjà 1 si les 2 dates sont égales en jour de semaine, et 0 pour un jour de we. Mathématiquement il a raison, mais un jour de semaine compte plus qu'un jour de we... Entré et livré le même jour un samedi excel te disait 0, un lundi il te disait 1.
Pour les stats est-ce meilleur ?
La différence là c'est que c'est 1, semaine et we pour faire plus simple, mais dès une différence de 1 jour la fonction reprend le dessus. Je n'ai pas controlé mais théoriquement pour une livraison le samedi tu auras 1 le samedi, ensuite 0 le dimanche, puis 1 le lundi etc.
Je ne connais pas tes jours de livraison et traitement possibles, si ça a une importance pour toi on peut calculer le jour pour mettre 0 ou 1 selon we ou semaine, toi seul sait ce qui est mieux.
eric

eriiic · Answer

PS: j'ai jeté un oeil à tes macros.
Tu cherches à accelerer le traitement mais tu as une tempo qui te fais perdre 2s à chaque enregistrement. Juste pour pouvoir voir la barre de progression se remplir...

tchulio · Answer

Je vois ce que tu veux dire.

Je viens de faire le test avec le fichier initial, formule , et par exemple si dans date d'entrée 10.10.08 et dans date de livraison 13.10.08 , dans temps de passage il y a 1.
Par contre effectivement avec la macro maintenant au lieu de 1, j'ai 2 jours.

Il n'y aurais vraiment pas possibilité de réussir pour qu'il n'y ait qu'un ? car je ne pense pas que ça aille pour mes stats lorsque je les présente a ma direction... MErci encore...

eriiic · Answer

oui, dans la dernière correction j'ai fait un copier/coller du code et le -1 avait sauté.
Mais ça n'enlève rien au fait que je compte tjs 1 pour un coli traité le même jour un samedi et qu'excel comptait 0 avant.
Correction de la correction ;-) :
http://www.cijoint.fr/cjlink.php?file=cj200810/cijZpDt5Qa.xls

tchulio · Answer

ben écoute là ça m'a vraiment l'air bien.
Je regarde tout ça demain au travail et je te redonne des nouvelles.

Bonne soirée a toi et merci beaucoup!

tchulio · Answer

Bonjour, alors j’ai bien regarder ta macro en faisant varier les dates, rien à redire, c’est parfait.

Par contre en la mettant sur mon programme complet, ça m’ennuie, il y a encore un bug.
En fait comme tu peux voir sur le fichier joint,  quand tu  cliques sur « Entrer un nouveau lot », il y a le Userform2 qui s’affiche comme dans l’ancien fichier, et lorsque la personne a terminé sont lot, elle va ensuite cliquer sur « Enregistrer un lot »,  là il y a le Userform8 qui s’affiche et s’est avec ce USF que la date de livraison s’enregistre. Mais lorsque je clique sur valider, il y a de nouveau le message d’erreur qui s’affiche.

En fait hier avant que tu fasses les modifications, le USF8 marchait bien et le USF2 plantait, maintenant c’est l’inverse. J’ai essayé 2-3 choses mais sans succès.

Une fois ce petit problème réglé, tout sera parfait et je te remercie vraiment de ton aide…

http://www.cijoint.fr/cjlink.php?file=cj200810/cij5GVqxc5.xls

eriiic · Answer

Tu m'as fait chercher avant de comprendre le pourquoi, faut tout faire en pas à pas pour avoir le message d'erreur...
Tu mets DateLiv en chaine de caractère et ce doit être une date pour Application.Run("ATPVBAEN.XLA!networkdays", DateEnt, DateLiv) - 1
Utilise cdate avant de l'inscrire (mais en te mefiant que souvent il forme une date au format anglais (mm/jj/aa) )
eric

tchulio · Answer

Excuse moi de répondre seulement maintenant, je rentre seulement du travail.

En fait dans le fichier que je t'avais mis en pièce jointe, je n'ai pas changé le code que tu avais mis, donc je ne vois pas trop ce que tu veux me dire... désolé...

eriiic · Answer

Bonsoir,

ce que je veux te dire c'est que l'erreur est dans TON code, lorsque tu inscris la dateliv dans une cellule.
Tu ecris un texte "18/08/08" et non pas une date 18/10/08.
Il faut mettre ....=cdate(ta_variable)

eric

tchulio · Answer

je pense que tu penses au code dans le Userfor8? mais le valeur est celle du calendar, et je ne sais pas comment le paramétrer différemment...

eriiic · Answer

Bonjour,

Dans le usf4 il fallait mettre :
Application.Worksheets("produits").Cells(ligne, 11) = CDate(TextBox11.Value)

De plus j'ai remplacé les dateent="" et les dateliv="" par =Null car la fonction ATPVBAEN.XLA!networkdays me générait des incompatibilités de type.
Et maintenant qu'il n'y a plus bcp de formules je pense que tu peux remettre le calcul automatique pour éviter les mauvaises surprises car je n'ai trouvé aucun .calculate dans ton projet.
http://www.cijoint.fr/cjlink.php?file=cj200810/cijKwWcYGS.xls

Bonne journée
eric

tchulio · Answer

Bonjour, 

Donc j’ai essayé au travail, effectivement lorsque je valide les 2 USF, les lignes s’insèrent correctement.

Par contre maintenant, les colonnes Z, AA, AL, AM,,AJ. Encore désolé de te déranger, c’est vraiment gentil tout ce que tu as fait pour moi !

eriiic · Answer

Par contre maintenant, les colonnes Z, AA, AL, AM,,AJ
et ?
Sois le plus précis possible, colonne par colonne

tchulio · Answer

en fait quand j'entre une date dans F, avant modif; normalement il y a une valeur qui s'inscrit dans Z, AB, AD, AE, AK, AL et AM ; et quand j'entrait une date dans K il y avait une valeur qui s'inscrivait dans AA, AC, AF, AG, AH, AJ.

Or maintenant lorsque j'entre une date en F il y a juste une valeur dans AB, AC, AD, AE, AK  puis quand une met une date dans K il y a juste une valeur dans AC, AF, AG, AH...

eriiic · Answer

Oufff, ça y est, c'était encore cette $%!?ç0!* de fonction ATPVBAEN.XLA!networkdays qui retournait une erreur en interprétant les dates comme américaines (dd/jj/yy).
Bon là c'est je pense réglé pour elle, reste quand même qu'elle cachait peut-être d'autres erreurs (une autre est apparue ensuite...) Contrôle tous les calculs dans différents cas de figure.

Je t'ai mis en commentaire 'Sheets("Menu").Select car la feuille n'existe pas et ça générait une erreur.
Et j'ai modifié un peu tes couleurs pour m'y retrouver...
http://www.cijoint.fr/cjlink.php?file=cj200810/cijpsaa36Z.xls

eric

tchulio · Answer

Bon ben éoute j'ai regardé ce matin, ça m'a l'air parfait tout ça !!!

Je refais 2-3 essais et te tiendrai informé :)

En tout cas merci beaucoup

eriiic · Answer

Ben on aura fini par y arriver... ;-)
Mais contrôle bien, c'est plus facile à corriger quand c'est encore frais en tête
eric

tchulio · Answer

pas de souci; si pas de problème d'ici lundi, c'est que tout est parfait :) bon w.e à toi!

Calcul automatique - Page 3

Discussions similaires

Newsletters