Remplacer des formules par VBA

[Aloha]

Bonjour,

Pour chaque personne dans une liste je calcule une feuille avec plein de valeurs, puis je copie les valeurs personne par personne dans un autre classeur.
Etant donné qu'il y a beaucoup de formules c'est assez lent.
Comment devrais-je procéder pour remplacer les formules par du code VBA pour accélérer les opérations?

Merci pour tout tuyau.

Aloha

 

[Aloha]

Re : Remplacer des formules par VBA

Re,

Ce n'est pas toi qui as fait cette faute de frappe, mais moi en recopiant cette ligne! Dans Excel c'est correct.

Mes excuses.

Et donc le problème est autre part. Voir ma remarque! Est-elle tellement à côté de la plaque?
Puisque je me demande comment Excel pourrait connaître la valeur que prend I.

A+

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Effectivement, mea culpa, j'ai fait une légère erreur dans mon code, en réalité, c'est

J = Plage.Count

donc

Application.SheetsInNewWorkbook = [SIZE=4][COLOR=red][B]J[/B][/COLOR][/SIZE]

Toutes mes excuses 😱.
A + 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Re,

La souris a dominé l'éléphant!
Il doit y avoir une autre erreur, puisque je reçois le message d'erreur que le même nom ne peut être donné à 2 feuilles: Excel ne change pas de nom, mais veut donner le même nom à la deuxième feuille qu'à la premère.


Il y a également un problème au niveau de l'opération copiant les noms.
Pour mieux comprendre ce que Excel fait: que signifie

Range(Cells(1, 1), Range("A1").End(xlToRight))

?

Bonne soirée

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,
Vu qu'il prend le contenu d'O3, c'est que soit on fait 2 fois la copie, soit la sélection de la feuille n'est pas bonne...
Range(Cells(1, 1), Range("A1").End(xlToRight)) signifie que je prends de la cellule "A1" (ou L1C1 en utilisant Cells) à la dernière cellule contigüe sur la droite.
Bonne journée 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Je pense comprendre la fonction de Range("A1").End(xlToRight): de la cellule A1 à la dernière cellule à droite dans la ligne 1
, mais je ne comprends pas la fonction de Cells (1,1), suivi d'une virgule suivie de l'expression Range, c'est-à-dire la combinaison de Cells(1,1) et de Range...


Avec mes pauvres connaissances VBA je lis: de la cellule A1 à la cellule A1 jusqu'à la dernière cellule à droite et dans cette compréhension il y aurait donc 2 fois A1! Et c'est cela que je ne comprends pas.

En ce qui concerne le tri, qui ne se fait pas: .Sort Key1 := Range ("O3").
Est-ce que les données qui doivent être triées, c.-à-d, les cellules O3, vers le bas, jusqu'à la dernière cellule contenant les noms, dans la feuille "Fiche-individuelle..." sont bien sélectionnées? Je ne comprends pas assez ce bout de code pour le savoir. En tout cas les noms sont bien collés au bon endroit.

Le plus gros problème est qu'il ne met pas les noms dans les feuilles, mais il met "Nom et prénom", et lorsqu'il renomme la 2e feuille cela bloque évidemment.

Quelque chose de bizarre qui se passe: dans la feuille Service1 les noms sont orientés verticalement (lire du bas vers le haut); lorsque Excel les a copiés, ils sont orientés horizontalement!
Bonne journée

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,

Je pense comprendre la fonction de Range("A1").End(xlToRight): de la cellule A1 à la dernière cellule à droite dans la ligne 1
Non, c'est LA dernière cellule à droite en partant de A1, d'où 2 fois A1 dans la formulation pour sélectionner la plage.
En ce qui concerne le tri, qui ne se fait pas: .Sort Key1 := Range ("O3").
Est-ce que les données qui doivent être triées, c.-à-d, les cellules O3, vers le bas, jusqu'à la dernière cellule contenant les noms, dans la feuille "Fiche-individuelle..." sont bien sélectionnées? Je ne comprends pas assez ce bout de code pour le savoir. En tout cas les noms sont bien collés au bon endroit.
Je sais, je me répète, mais je n'ai aucune idée de ce qui est dans O3 ou S3, etc. sans un petit fichier test...
Le plus gros problème est qu'il ne met pas les noms dans les feuilles, mais il met "Nom et prénom", et lorsqu'il renomme la 2e feuille cela bloque évidemment.
Idem ci-dessus...
Quelque chose de bizarre qui se passe: dans la feuille Service1 les noms sont orientés verticalement (lire du bas vers le haut); lorsque Excel les a copiés, ils sont orientés horizontalement!
Ton code de départ, c'est Transpose:=True donc forcément, le colage est transposé. Sinon, il fallait mettre False

Bon courage 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonsoir,

J'ai ajouté deux feuilles "Service1" et "Service2" dans mon fichier initial. Elles correspondent, en ce qui concerne les parties qui nous intéressent ici, aux feuilles originales.

Ce que je décris comme effet bizarre, c'est dans le fichier "Service1" que je l'observe, pas dans la feuille dans laquelle les données sont collées.

Bonne soirée

Aloha

PS: je n'arrive pas à comprimer le fichier assez: il était à 48,9 Kb; après plusieurs actions j'ai réussi à le baisser à 48,8, mais il est tout de même trop grand, alors que le maximum est justement 48,8!!
Dommage que RAR n'est pas supporté; avec ce format on arrive à comprimer beaucoup plus.

 

[Aloha]

Re : Remplacer des formules par VBA

2e essai: le fichier est toujours 48,8, mai il est passé!
Que Dieu comprenne pourquoi

 

[JNP]

Re : Remplacer des formules par VBA

Bonjour Aloha 🙂,
Sans être lumineux, ton fichier éclaire un peu ma lanterne 😛.
Si je comprends bien, le but est de prendre les noms sur la feuille de service, d'établir une fiche par personne dans un autre fichier en cassant les liaisons avec le PasteSpecial. Ton copier/coller transposé sert à pouvoir trier en vertical et la feuille Fiche individuelle 6 périodes ne sert qu'à ça. La feuille Switch, par contre ???
Tu pourrais trier la feuille Service 1 pour commencer, ce serait plus simple

Sheets("Service1").Range(Cells(1, 2), Range("B1").End(xlToRight)).Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, [B][COLOR=red]Orientation:=xlLeftToRight[/COLOR][/B], _
    DataOption1:=xlSortNormal

Attention, je n'ai sélectionné que la première ligne...
A + 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonjour,

Non, non, il ne faut surtout pas trier dans la fiche Servicex, car dans les ligne en-dessous il y a plein de données concernant le contrat de travail des personnes, et un tri sur les noms serait donc la catastrophe!

Il ne devrait pas y avoir de problèmes pour faire un tri.

Mais, mais,...plus élégant est sûrement possible: en réalité il n'y aurait pas besoin de copier et coller ces noms, mais de les placer un par un, par VBA, dans la feuille "Fiche individuelle 6 périodes", de copier cette feuille, de la coller dans le nouveau classeur, et prendre le prochain nom dans "ServiceX" etc.

Le but de toute l'opération, c'est de produire un classeur où chaque personne d'un service a sa feuille avec tous les chiffres en relation avec leur travail.

Dans la feuille switch on détermine seulement pour quel service ont veut les feuilles individuelles: il y a un bouton par service.
Et dans la feuille individuelle on collecte toutes les données depuis d'autres feuilles, surtout "Database", et puis, comme tu le dis, par PasteSpecial les liaisons sont cassées pour permettre de coller le tout dans un nouveau classeur.

Bonne après-midi

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,

Non, non, il ne faut surtout pas trier dans la fiche Servicex, car dans les ligne en-dessous il y a plein de données concernant le contrat de travail des personnes, et un tri sur les noms serait donc la catastrophe!
Si tu tries les données en même temps, il n'y aura pas de catastrophe, uniquement une feuille bien présentée...
Mais, mais,...plus élégant est sûrement possible: en réalité il n'y aurait pas besoin de copier et coller ces noms, mais de les placer un par un, par VBA, dans la feuille "Fiche individuelle 6 périodes", de copier cette feuille, de la coller dans le nouveau classeur, et prendre le prochain nom dans "ServiceX" etc.
On pourrait par exemple stocker les noms sans faire de copier/coller (et même les trier).
Le but de toute l'opération, c'est de produire un classeur où chaque personne d'un service a sa feuille avec tous les chiffres en relation avec leur travail.
Donc le simple fait de mettre le nom dans la bonne cellule déclenche bien le remplissage de la feuille en fonction ?
Dans la feuille switch on détermine seulement pour quel service ont veut les feuilles individuelles: il y a un bouton par service.
Mais alors, pourquoi changer une cellule de la feuille Switch dans la macro ?
Et dans la feuille individuelle on collecte toutes les données depuis d'autres feuilles, surtout "Database", et puis, comme tu le dis, par PasteSpecial les liaisons sont cassées pour permettre de coller le tout dans un nouveau classeur.
Ok, je vois qu'on avance 😉

A plus 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Bonsoir,

Je ne vois pas pourquoi il faudrait absolument trier dans les feuilles Service; avec les noms et les données afférentes sont produites des feuilles dans lesquelles les gens introduisent mensuellement leurs heures, qui sont ensuite copiées dans Database; si je trie les noms dans Servicex, les noms de ces fiches seront inversés; il y a donc une raison pour ne pas y toucher

On pourrait stocker les noms... Justement, mais comment?

Exact: le fait de mettre le nom déclenche la mise à jour des formules.

Switch: c'est sans grande importance; sauf que, lorsque je mets ServiceX dans la cellule correspondante, une feuille jusqu'ici pas mentionnée (parce que sans importance pour cette opération) du nom de "Personnel", contenant quelques centaines de lignes de formules, va chercher les noms dans ServiceX ainsi que les autres données, comme p.ex. le nombre d'heures hebdomadaires d'une personne , avec 5 changements des heures possibles en une année, et tous les paramètres sont calculés par ces formules. Si tu regardes bien les formules dans "Fiche individuelle 6 périodes" tu en trouveras qu se réfèrent à la feuille "Personnel" e.a. pour afficher les heures de travail et de congé annuelles d'une personne. Et comme il faut que les noms du bon service doivent être chargés dans Personnel pour que la fiche individuelle soit remplie, j'ai mis une simple formule de renvoi vers "Switch" et voilà pourquoi je mets le bon service dans Switch. Légèrement compliqué, mais le système fonctionne depuis 2004 ou 2005 sans faille et gère les heures d'une demi-douzaine de services.

On progresse? Sans toi je ne progresserai pas plus; j'ai atteint mes limites avec le système que j'ai mis debout. J'ai besoin d'aide pour simplifier et accélérer par VBA.
E.a. je me demande toujours comment je pourrai remplacer les formules par du code VBA qui accélérerait les opérations de beaucoup.

A+

Aloha

 

[JNP]

Re : Remplacer des formules par VBA

Re 🙂,

On pourrait stocker les noms... Justement, mais comment?
Dans un tableau VBA, je vais y jeter un oeil, vu que tu ne souhaites pas trier la feuille de départ
On progresse? Sans toi je ne progresserai pas plus; j'ai atteint mes limites avec le système que j'ai mis debout. J'ai besoin d'aide pour simplifier et accélérer par VBA.
On progresse sur le fond, pour la forme, ça prendra du temps, mais bon...
E.a. je me demande toujours comment je pourrai remplacer les formules par du code VBA qui accélérerait les opérations de beaucoup.
Précise quelles formules tu veux remplacer... Pour ce que j'en vois actuelllement, c'est juste du copier/coller

A plus 😎

 

[Aloha]

Re : Remplacer des formules par VBA

Re,

Je parle surtout de cette multitude de formules SOMMEPROD() que tu vois dans "Fiche individuelle"

A+

Aloha

 

[Aloha]

Re : Remplacer des formules par VBA

Re,

Je parle surtout de cette multitude de formules SOMMEPROD() qui se trouvent dans "Fiche individuelle"

A+

Aloha