Microsoft 365 Copier/Coller sous multicritères

[M.Ben]

Bonjour la communauté XLD,

J'ai un soucis pour prioriser des actions dans ma macros, j'ai donc besoin de vos lumières.

Je m'explique, je développe une feuille Excel qui répertorie une liste d'équipements (Actionneurs, capteurs, autres) dans une première feuille.
Dans ma seconde feuille j'effectue un bilan de puissance qui reprend certains éléments de la première feuille.

Actions à réaliser:
- Identifier dans la feuille 1 les actionneurs, les capteurs etc,
- Compte le nombre de lignes pour chaque catégorie,
- Compare dans la seconde feuille si pour chaque éléments il y a suffisamment de lignes,
- En fonction de cela, ajoute ou supprime dans la feuille 2 des lignes
- Une fois que les lignes correspondes, copie certains éléments de la feuille 1 à la feuille 2 (N°PID, Nom composant ....)

Je monte mon programme petit à petit, et actuellement je bloque sur la création des lignes. J'arrive à créer des lignes pour la rubrique capteur et actionneur indépendamment l'une de l'autre. Par contre actuellement quand j'exécute ma macro pour la catégorie actionneur les lignes sont rajouter dans la catégorie capteur de la feuille 2.
Alors que si je mets en commentaire la partie qui traite les capteurs, les lignes qui traite les actionneurs sont rajoutées au bonne endroit (dans la catégorie actionneurs).

Je n'arrive pas à donner un ordre d'exécution (dire que d'abord tu traites les capteurs, puis quand tu as finis tu traites les actionneurs etc).

PS: pour le moment je traite la partie création de lignes, le copier/ Coller de données je le traiterai après.

J'ai joint le fichier type, et j'espère que mes explications sont compréhensible.

Pour la plus part de mes programmes, j'utilise la fonction apprentissage et je me sert de ce forum pour voir si des sujets similaire ont été traité.

Question fâcheuse: J'ai souvent vu dans les différents topics de ce forum et ailleurs que la fusion de cellules était à proscrire au maximum, cependant, dans ma feuille je souhaiterai fusionner certaine d'entre elles pour des question d'esthétique. est-ce qu'il existe une manière de dire en début de programme tu défusionnes les cellules fusionnées pour exécuter le programme et à la fin tu les fusionnes de nouveaux?

Encore merci et désolé pour le pavé

 

[sylvanu]

Bonjour M.Ben,
Compter le nombre de lignes en "A" ou "C", c'est facile.
En revange, calculer le nombre de lignes à insérer est difficile avec votre fichier.
Par ex en BP vous avez "Transmetteur de pression", là c'est simple car cet item n'existe pas dans LAC. Mais quand vous aurez Pompe ou Agitateur, comme vous en avez plusieurs comment savoir si la ligne existe ou non. Il faudrait un critère unique pour dire cette ref existe je ne la copie pas, cette ref n'existe pas je la copie.
On pourrait utiliser Rep.PID mais comme il y a plusieurs "-" ce n'est pas possible.

a fusion de cellules était à proscrire au maximum,

Exact c'est toujours la galère à traiter, mais c'est faisable.

est-ce qu'il existe une manière de dire en début de programme tu défusionnes les cellules fusionnées pour exécuter le programme et à la fin tu les fusionnes de nouveaux?

Là, ce serait la galère, et pas obligatoirement utile.

NB:
Pour compter les items, il y a plus simple que des boucles :

Sub Compte()
    With Sheets("LAC")
        count_A = Application.CountIf(.[A:A], "A*")
        count_C = Application.CountIf(.[A:A], "C*")
    End With
End Sub

Ce qui revient à faire dans la feuille =NB.SI(A:A;"A*") et =NB.SI(A:A;"C*"), et ça fait bien 16 et 4.

 

[M.Ben]

Bonjour Sylvanu,

Compter le nombre de lignes en "A" ou "C", c'est facile.

Oui ma macro le fait déjà via des boucles.

NB:
Pour compter les items, il y a plus simple que des boucles :
VB:
Sub Compte()
With Sheets("LAC")
count_A = Application.CountIf(.[A:A], "A*")
count_C = Application.CountIf(.[A:A], "C*")
End With
End Sub

Merci pour cette alternative pour le comptages d'items. Je remplacerai mes boucle par votre solution. Ça sera toujours plus agréable à lire que des boucles (moins de lignes).

En revange, calculer le nombre de lignes à insérer est difficile avec votre fichier.

Mon fichier compte déjà le nombre de ligne à insérer, pour cela je compte dans la feuille "BP" le nombre de ligne qui contiennent le mots capteur ou Actionneurs ou autres. Que je compare avec les lignes qui contienne la lettre A ou C de la feuille "LAC".

If ws_BP.Cells(y, 2) = "Capteur" Then
Count_Name_1 = Count_Name_1 + 1 'Compteur de ligne pour le mot capteur
Capteur = count_C - Count_Name_1 'Permet de calculer la différence de ligne entre feuille LAC et BP

Avec des variables je renseigne le début de chaque rubrique de la feuille BP.

Dep_Ligne_Capteur = 12
Dep_Ligne_Actionneur = 14 + c
Dep_Ligne_Autres = 16

C'est après ça que j'ai un soucis. ma première boucle insère dans le rubrique capteur le nombre de ligne de différence qui est calculé.

'Gestion des lignes concernant les capteurs
If Count_Name_1 < count_C Then
ws_BP.Activate
For c = 1 To Capteur
ws_BP.Range(ws_BP.Cells(Dep_Ligne_Capteur, 1), ws_BP.Cells(Dep_Ligne_Capteur, 24)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
'Modifie la bordure inférieur
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 2
.TintAndShade = 0
.Weight = xlThin
End With
Next c

J'ai plusieurs en fonction des cas ligne feuille BP < ligne feuille LAC, ligne feuille BP = ligne feuille LAC et ligne feuille BP > ligne feuille LAC.
J'ai commencé à faire pareil pour la rubrique actionneur. Mon problème c'est qu'il insère les lignes dans la rubrique capteur et non actionneur. Pourtant j'ai essayé de prendre la valeur de ligne que je rajoute dans la rubrique capteur comme décalage.

Dans mon programme si on met toutes la partie qui concerne la gestion des lignes capteurs et qu'on exécute ça fonctionne bien. C'est à dire que les lignes qui concerne les actionneurs sont insérées au bon endroit.

 

[sylvanu]

Je pense que le souci vient du calcul de Dep_Ligne_Actionneur qui est prématuré, car ensuite vous insérez des lignes, et donc cette valeur devient fausse.
J'ai réinitialisé cette valeur juste avant de s'en servir, pouvez vous tester, avec :

    Dep_Ligne_Actionneur = Application.Match("Actionneurs", [A:A], 0) 'Premier actionneur
    If Count_Name_2 < count_A Then
        ws_BP.Activate
        ....

 

[M.Ben]

Je pense que le souci vient du calcul de Dep_Ligne_Actionneur qui est prématuré, car ensuite vous insérez des lignes, et donc cette valeur devient fausse.
J'ai réinitialisé cette valeur juste avant de s'en servir, pouvez vous tester, avec :

    Dep_Ligne_Actionneur = Application.Match("Actionneurs", [A:A], 0) 'Premier actionneur
    If Count_Name_2 < count_A Then
        ws_BP.Activate
        ....

Ça fonctionne parfaitement, merci sylvanu.

Je vais pouvoir continuer à finaliser le dimensionnement de la table de ma feuille BP et après réfléchir à comment copier certaines donné de la feuille LAC à la feuille BP.

Je passe cette rubrique en résolu, si je rencontre d'autres blocages, je les posterai dans ce topic vu que ça sera pour le même fichier.

D'ailleurs, si tu as des pistes ou conseils pour cela je suis preneur. Car j'hésite à séparer le copier-coller des données et le dimensionnement de la table BP en 2 modules séparés ou tous dans le même module.

Merci à toi.

 

[sylvanu]

J'ai le même questionnement qu'au post #2.
Je ne vois toujours pas comment vous allez savoir qu'un item de LAC est présent ou non dans BP puisqu'il n'y a pas d'identifiant.
Par ex, si dans BP vous avez Pompe, comment savoir quand vous lirez Pompe dans LAC vous saurez si c'est celui là qui a été copié ou non.

Mais bon, wait and see.

 

[M.Ben]

J'ai le même questionnement qu'au post #2.
Je ne vois toujours pas comment vous allez savoir qu'un item de LAC est présent ou non dans BP puisqu'il n'y a pas d'identifiant.
Par ex, si dans BP vous avez Pompe, comment savoir quand vous lirez Pompe dans LAC vous saurez si c'est celui là qui a été copié ou non.

Mais bon, wait and see.

Je pensais sélectionner les cellules ligne par ligne qui m'intéresse les copier puis les coller dans l'ordre, ça détecte que la ligne est pleine ca copier la prochaine dessous (alors c'est peut-être plus compliqué qu'il n'y parait).
Ou peut-être faire un concaténation ligne par ligne du mot avec le repère des éléments feuille LAC, du coup ça permettrait qu'il n'y ait pas de doublons ou de conflit avec des termes identiques.

Je vais essayer, je posterai le résultat ou mon problème .

 

[sylvanu]

Désolé, je pense que je vous embrouille inutilement.
Mais j'ai vu que dans BP il y avait des données. Si c'est le cas et qu'il faut rajouter les absents, alors il y a un souci, car les données présentes devront toujours être en corrélation avec la Ref.
Si BP est vierge de données lors du transfert, alors ça roule.

 

[M.Ben]

Désolé, je pense que je vous embrouille inutilement.
Mais j'ai vu que dans BP il y avait des données. Si c'est le cas et qu'il faut rajouter les absents, alors il y a un souci, car les données présentes devront toujours être en corrélation avec la Ref.
Si BP est vierge de données lors du transfert, alors ça roule.

Ah c'est bon je vois mieux et je sens venir le problème du coup, oui si des données sont déjà présentes en feuille BP il va y avoir un gros problème.
Merci pour cette bonne remarque, à voir comment je vais pallier ce problème. Un clear contents règlerai le problème.
Le problème de cette solution c'est que si des lignes qui sont déjà renseignées m'intéresse, il va falloir que je les complète de nouveau .

Je publierai l'avancement de mon fichier quand celui-ci sera fini ou en PLS .

C'est toujours intéressant d'avoir les remarques d'autres personnes .

 

[sylvanu]

Ou un clear si les données sont obsolètes, ou il faut impérativement en feuille BP chaque ligne ait une référence unique, par ex un ref constructeur, ou autre.

 

[M.Ben]

Comme convenue, le fichier après quelques prise de tête (presque fonctionnelle).
J'ai juste un petit problème de copier collé pour la rubrique actionneur, ça me copie une ligne en trop.
Je n'ai pas encore trouvé la raison.
Il me faut aussi optimiser certaines choses je pense (si je duplique les feuilles etc pour repérer où copier-coller ...). Et pour le moment c'est un clear que je fais.
Encore pas mal de boulot je pense.
Mais si vous avez des remarques et des conseils n'hésitez pas faire vivre ce sujet

 

[sylvanu]

Bonsoir,
Lorsque vous avez N éléments et que vous comptez de 0 à N, vous comptez N+1 .
Une PJ à tester avec :

For i = 0 To Count_Name_2 - 1
et
For i = 0 To Count_Name_1 - 1

Ensuite j'ai pas pu m'empêcher, ces trois lignes :

For l = 11 To DerniereLigne2
    ws_BP.Rows(l).RowHeight = 35
Next l

peuvent être remplacé par :

ws_BP.Range("A" & 11 & ":A" & DerniereLigne2).RowHeight = 35

Enfin, en début de macro, ne pas oublier :

Application.ScreenUpdating = False

Qui fige l'écran et permet d'accélerer.
Sur mon PC, votre macro passe de 1.96s (sans) à 0.45s (avec).

A vous lire.

 

[M.Ben]

Bonjour,

C'est un plaisir de vous lire et vos réponses sont toujours bien claire, novice que je suis cela m'aide énormément.
Effectivement, votre fichier tourne plus rapidement que le miens.
Je n'ai aucune notion de ce côté-là (savoir si le fichier est optimisé ou non niveau temps d'exécution).
Sur l'ensemble des quelques projets que j'ai monté, je construis mon codage et si le résultat final est bon je m'arrête là, c'est peut-être un défaut.
D'ailleurs vous passez par où pour voir le temps d'exécution.

Je ne connaissais pas cette alternative, c'est plus jolie que ma petite boucle qui fait un peu tache .

ws_BP.Range("A" & 11 & ":A" & DerniereLigne2).RowHeight = 35

D'autre part, si je souhaite dupliquer ces 2 onglets qui fonctionnent ensembles pour créer un autre ensemble (En soit une autre armoire électrique), le mieux serait de créer un autre bouton pour dupliquer les onglets et remplacer les ws_BP et ws_LAC qui qualifie l'onglet de travail par une combo box ? Ou le plus simple, c'est de rester sur 1 onglet par armoire (Cette solution multiplierai juste le nombre de fichier par projet).

Bien à vous.

 

[sylvanu]

Bonjour,
1- En vous lisant j'ai vu une maladresse dans mon code. Comme la ligne 11 ne bouge jamais, ceci est inutile:

ws_BP.Range("A" & 11 & ":A" & DerniereLigne2).RowHeight = 35

Ceci est plus simple.

ws_BP.Range("A11:A" & DerniereLigne2).RowHeight = 35

Voir PJ.
2- Pour la mesure du temps c'est simple. Le VBA possède un compteur qu'on appelle Timer. Ce compteur compte tout le temps et ne s'arrête jamais.
Pour mesurer un temps il suffit de faire :

Sub Copier_Coller()
    T0 = Timer
    '....
    '....
    MsgBox Timer - T0
End Sub

En début de macro on "capture" la valeur de timer, et à la fin on fait la différence de la valeur du timer moins sa valeur initiale.
A noter qu'on peut mettre ces instructions n'importe où suivant ce qu'on veut mesurer. d'autre part la résolution du timer est de 4ms, donc pas très précis. A ne pas utiliser pour des temps très court sa valeur serait erronée.
3- Le choix est entre plusieurs fichiers de deux onglets, ou un fichiers plein d'onglets.
Impossible de répondre, c'est un choix.
- Si un seul fichier et plusieurs intervenants, alors il sera difficile que plusieurs intervenants modifient le fichier en même temps.
- Si un fichier par armoire. Alors en cas de modifs il faut penser à rectifier l'ensemble des fichiers.
- A vous de voir si la liste LAC est commun à toutes les armoires ou non, car le cas échéant il faut reprendre chaque liste en cas d'évolution.
Ou rajouter une colonne dans LAC avec le N° de l'armoire, il n'y aurait qu'une liste à gérer.
Cla ferait une feuille LAC enrichie et un onglet par armoire.
Donc comme vous le voyez ... à vous de voir.

 

[M.Ben]

Que de connaissance, merci pour toutes ces informations.

Si je comprends bien pour le timer, il suffit de mettre TO = Timer en début de macro et mettre le message box au niveau de là où nous souhaitons avoir le retour (exemple en fin de boucle ou en fin de programme), c'est bien ça ?

Autre question, je vois que dans vos lignes de code, il y a des couleurs je trouve ça pratique pour vite repérer l'information, comment faites-vous car les miennes sont toutes noir et bleu pour les fonctions VBA.

Et la dernière, il y a pas une case à cocher dans les options pour avoir la proposition des variables créer un peu comme les propositions qui sont faites quand vous faites un .R, un menu contextuel apparait avec plusieurs propositions.

Si ma demande est hors sujet, je m'en excuse. Ou si vous avez un liens sur lequel je pourrais aller lire un peu toutes les personnalisations qui sont faisables.

En PJ, le fichier avec en plus de vos remarques, une concaténation de cellules qui prend en compte les éléments dans la colonne Accessoire, si des éléments sont renseignés ou non.

Je continuerai à publier l'évolution du fichier jusqu'à la version abouti à 100%. Car il y a tellement de fonction utile à y rajouter.