Discussion :

[Darkangel2014]

Bonjour,

merci pour votre lien sur powerquery j'ai effectivement testé et effectivement pas très compliqué de créer les requêtes.
je tente désormais de mettre en application la méthode VBA que je préfère.

Aussi j'ai tenté d'examiner vos fichiers et les comprendre et tente de mettre en application la méthode dans mon fichier de test.
Si je comprends bien, il s'agit de fusionner mes trois tableaux projects / investigators / budget dans un fichier temporaire pour ensuite les trier et en ressortir les infos requises.

Aussi j'ai bien remis en forme les trois tableaux de mon fichier test et j'ai créer un feuille autre dans laquelle j'ai mis un tableau temporaire dont je ne connais pas la taille de base donc j'ai sélectionner un champ large... et encore une autre feuille destiné au tableau final que j'ai appelé Report
test.xlsm

je commence donc par le nettoyage du tableau temp
le dénominateur commun à mes onglets est l'id projet pour information

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
  ' Nettoyage du tableau temporaire
  If Not Range("T_Temp").ListObject.DataBodyRange Is Nothing Then Range("T_Temp").ListObject.DataBodyRange.Delete

premier soucis avec cette ligne, j'ai une erreur d'éxécution 1004... ca commence bien


ensuite et c'est là que je me pose des questions:
me faut il ici déclarez mes 3 tableaux à fusionner ?

je me base donc sur votre exemple et commence par le tableau projet

' Copie des taches dans le temporaire
Set Target = Range("T_Temp").ListObject.ListRows.Add().Range(1)
Target.Resize(Range("Projects").Rows.Count, 2).Value = Range("Projects").Value


me faut il ici directement faire la même chose avec Investigators et Budget?
Target.Resize(Range("Investigators").Rows.Count, 2).Value = Range("Projects").Value


merci par avance pour vos éclarcissements

[jurassic pork]

hello Pierre,

Evidemment, tu la crées et la supprime après, mais ça induit du VBA là où ce n'est pas forcément nécessaire. De plus, si l'ordre de tri change, ça impose une modification du code VBA alors que la colonne d'ordre peut être modifiée par "n'importe quel utilisateur" sans connaissance de VBA. De plus, ça ne résout pas le problème des statuts à ne pas prendre en compte

1 - Si l'ordre de tri change pas besoin de modifier le code VBA c'est la liste de tri personnalisée dans le classeur qui est utilisée.
2 - Les status à ne pas prendre en compte sont dans la liste Filtre.

Ami calmant, J.P

[Pierre Fauconnier]

[...]

Je n'avais pas vu que tu utilisais une plage pour créer ta liste.

Et tout cela

• sans tableaux structurés alors qu'ils simplifient l'utilisation d'Excel et le codage en VBA;
• en obligeant à utiliser une feuille TEMP alors qu'elle n'est pas nécessaire;
• en utilisant du VBA (et donc de la fragilité) là où ça n'est pas nécessaire;
• avec en plus plein de données en dur dans le code qui le rendent encore plus fragile et instable;
• en utilisant cette syntaxe [M2];
• ...

Franchement, très peu pour moi, je reste sur l'idée de ma colonne de tri que tout utilisateur normal d'Excel va comprendre et va pouvoir modifier, qui permet en plus de se passer de VBA si on veut...


Mais libre à chacun de faire ses choix, quitte à ce qu'ils soient mauvais. Pour moi, la programmation, c'est la rigueur, la réflexion, la recherche de la meilleure solution (je conçois que "meilleure" soit une notion très subjective), la recherche, surtout, de la fiabilité (donc le moins de code possible, le moins de "fantaisies" possible, coller au mieux à Excel: Quand on programme en VBA pour Excel, on pense Excel avant de penser VBA, l'écriture de code réutilisable et donc testé et testable, ...). Je ne vais pas me faire des amis en écrivant cela et chacun en pense ce qu'il veut, mais j'ai parfois beaucoup de mal à comprendre certaines solutions proposées et l'objectif poursuivi à les proposer.

[Darkangel2014]

Bonjour Jurassic Pork

Merci pour votre proposition.
je tente de la mettre en place mais ai le droit a une erreur 1004
test - Copie.xlsm

une idée du pourquoi svp?

[Pierre Fauconnier]

Darkangel,

Chez moi, pas d'erreur avec ce code.


Tiens, au passage... Comment vas-tu agir lorsque tu auras ajouté des lignes à ton tableau? Parce que le code que tu utilises, il va de la ligne 1 à la ligne 78... Tu vas aller modifier ton code chaque fois que tu vas ajouter ou supprimer des lignes?


On pourrait rester sérieux ? Et, par exemple, utiliser les tableaux structurés et coder proprement?

[Darkangel2014]

bizarre car de mon coté j'ai toujours la même erreur

je n'ai pas abandonné votre méthode du début mais rencontrais également des soucis mentionnés dans un post précédent ou je posais par ailleurs des questions

je testais cette autre méthode pour voir comment elle fonctionne (ca peut toujours etre utile un jour )
pour repondre à votre question cependant, non je ne me limiterais pas à la ligne 78 mais on peut indiquer jusqu'a la derniere ligne du tableau .

[jurassic pork]

moi je ne suis pas un spécialiste Excel alors je proposais cela pour voir et pour explorer une autre possibilité
D'ailleurs j'ai un drôle de phénomène quand j'exécute ma macro : Excel se ferme au bout de quelques minutes sans prévenir. Une idée sur la cause possible ? Un virus dans le fichier de DarkAngel ? Un bug avec les listes personnalisées ?

Ami calmant, J.P

[Pierre Fauconnier]

Utilise la solution que tu préfères, c'est ton classeur et c'est toi qui te débrouillera avec

Bien sûr, on peut déterminer la dernière ligne utilisée, encore faut-il savoir comment...


Démonstration de l'utilité des tableaux structurés

SANS tableaux structurés, recherche de la plage du tableau





Arf... Pas bon à cause de l'absence de prénom sur la dernière ligne... En plus de la longueur de la ligne de code...

On devrait plutôt utiliser CurrentRegion...



Oui mais non, car si je déplace la plage pour mettre un titre au dessus... Aïe aïe aïe






Si je déplace le tableau dans la feuille pour des raisons de présentation, pas bon non plus... Idem si je le déplace sur une autre feuille...





Si je renomme la feuille... Ouille ouille ouille






On le voit, on a pas mal de problèmes et d'instabilité...

[Pierre Fauconnier]

Solution avec tableau structuré...






Déplacement? Même code





Renommage de la feuille? Déplacement sur une autre feuille? Même code



Ajout de colonnes? Même code






La seule restriction, c'est de ne pas renommer le tableau, mais comme dit, par d'autres ou par moi, dans d'autres discussions, normalement, le nommage des tableaux et des colonnes se réfléchit à la conception, et il y a peu de raisons de renommer un tableau t_Contacts en quoi que ce soit d'autre...


J'espère que cette démo en deux messages permettra de bien comprendre l'utilité de travailler avec des tableaux structurés, et de penser Excel lorsque l'on développe en VBA pour Excel

[Pierre Fauconnier]

moi je ne suis pas un spécialiste Excel alors je proposais cela pour voir et pour explorer une autre possibilité

JP,

Je n'ai rien contre toi, crois-le bien, et mes "attaques" ne sont pas personnelles (en plus, j'ai aussi commencé avec un ZX81 ). Je veux juste insister sur les bonnes pratiques et ce que je considère être la bonne conception d'un classeur et d'un code...

D'ailleurs j'ai un drôle de phénomène quand j'exécute ma macro : Excel se ferme au bout de quelques minutes sans prévenir. Une idée sur la cause possible ? Un virus dans le fichier de DarkAngel ? Un bug avec les listes personnalisées ?

Ami calmant, J.P

N'ayant jamais eu, sauf cas d'école en formation, à programmer des listes personnalisées, je ne saurais pas te renseigner sur un bug éventuel lors de la création/suppression d'une liste perso en VBA

[Darkangel2014]

merci a vous pour cette demonstration en terme de comparaison.
je soutien votre méthode avec les tableaux structurés et souhaiterais donc m'orienter dans cette voie.

seulement j'ai tout de même les problèmes/ question soulevées dans mon post antérieur


ps: je ne pense pas avoir de virus je tourne sur mon pc de boulot bien protégé
quand je teste la méthode de jurassic sur ma base pour générer la page temporaire j'ai la meme erreur ..

[Darkangel2014]

j'ai trouvé l'erreur pour tester la macro de jurassic:
il faut mettre des parenthèses sinon de mon coté ça plante

Application.AddCustomList (rngLstTri)

toujours est il que le filtre se place sur la 2 ème ligne et pas la premiere ou les champs titres sont...

[jurassic pork]

et moi j'ai trouvé pourquoi j'ai ce phénomène :

Quand j'exécute ma macro : Excel se ferme au bout de quelques minutes sans prévenir

C 'est bien un problème Excel voir ici

Custom List Deletion Causing Excel to Crash When Save File

En fait c'est quand arrive l'enregistrement automatique que j'ai un crash.
La solution proposée :

A coworker found the answer for me, so I thought I'd share in case anyone else is seeing this. After each sort I have to add the following:
ActiveSheet.Sort.SortFields.Clear

donc voilà ce que je fais maintenant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
    r.Sort key1:=[M2], order1:=1, ordercustom:=Application.CustomListCount + 1, _
           key2:=[G2], order2:=1
    Sheets("Temp").Sort.SortFields.Clear
    Application.DeleteCustomList Application.CustomListCount

Ami calmant, J.P

[Pierre Fauconnier]

Super. Merci JP

Cela dit, avec un tableau structuré (j'y tiens), on n'est pas obligé de passer par une liste perso, on peut passer par une chaine de caractères que l'on recompose au besoin, alors que sur un objet Range, on ne peut, je crois, utiliser qu'une liste personnelle.

Sur base des tableaux ci-dessous:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Test()
  Dim SortList As String
  Dim i As Long
 
  For i = 1 To Range("t_Services").Rows.Count
    SortList = SortList & Range("t_Services[service]")(i) & ","
  Next i
  SortList = Left(SortList, Len(SortList) - 1)
 
  With Range("t_Contacts").ListObject.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("t_Contacts[Service]"), CustomOrder:=CStr(SortList)
    .Apply
  End With
End Sub

[jurassic pork]

toujours est il que le filtre se place sur la 2 ème ligne et pas la premiere ou les champs titres sont...

c'est parce que le range r commence en ligne 2.
Avec cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
  Sheets("Temp").Range("A1:N78").AutoFilter Field:=13, Criteria1:="<>" & Sheets("Temp").Range("Q2")

le filtre se place sur la première ligne

[Pierre Fauconnier]

c'est parce que le range r commence en ligne 2.[...]

Perso, je précise le Header dans la commande:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("a1:b8").Sort range("a1:a8"),header:=xlYes

Avec le tri personnalisé sur base d'une chaine et le fait que pour un listobject, on n'a pas besoin de préciser le header, ça commence à faire pas mal d'avantages pour le tableau structuré ( c'est juste un )

[Darkangel2014]

merci jurassic le fichier temp est désormais bien fonctionnel


néanmoins j'attends désormais de voir par comparaison la méthode avec tableaux structurés mais attends l'aide de Pierre quant aux questions soulevées

[Pierre Fauconnier]

Si je comprends bien, il s'agit de fusionner mes trois tableaux projects / investigators / budget dans un fichier temporaire pour ensuite les trier et en ressortir les infos requises.

Oui

j'ai créer un feuille autre dans laquelle j'ai mis un tableau temporaire dont je ne connais pas la taille de base donc j'ai sélectionner un champ large

Ce n'est pas nécessaire puisque le tableau est vidé avant traitement

et encore une autre feuille destiné au tableau final que j'ai appelé Report

Ok

premier soucis avec cette ligne, j'ai une erreur d'éxécution 1004... ca commence bien

Probablement une faute de frappe pour le nom du tableau

me faut il ici déclarez mes 3 tableaux à fusionner ?

Je ne sais pas ce que tu entends par "Déclarer"... Tu dois amener le premier tableau dans ton temp, puis écrire en VBA les formules qui permettent de récupérer les données des autres tableaux, comme je l'ai montré avec .Formula = "=Index..." (qui place un INDEX/EQUIV dans les colonnes en fonction des tableaux qui contiennent tes valeurs. Si les clés communes sont dans la première colonne, tu pourrais utiliser .Formula= "=VLOOKUP(...) (qui place un RECHERCHEV en VBA) si cette fonction t'est plus familière. Perso, je préfère INDEX/EQUIV car je ne suis ainsi pas lié à la position de mes colonnes (C'est toujours une recherche du code le plus stable, en fait).

Après, lorsque les formules ont été ajoutées et que j'ai mon tableau complet avec toutes les colonnes liées, il reste à filtrer, trier et pousser dans le tableau de résultat pour les colonnes qui sont reprises dans le résultat.

Question: Connais-tu RECHERCHEV ou INDEX/EQUIV en Excel?

[Pierre Fauconnier]

Darkangel, tu sais remettre ton fichier?

EDIT: Je les ai retrouvés, mais je persiste et signe, Power Query serait vachement plus simple...

quelles sont les colonnes reprises dans le fichier DEST?

[Darkangel2014]

ok merci pour cette confirmation Pierre.

Ce n'est pas nécessaire puisque le tableau est vidé avant traitement

ok mais du coup je déclare ce tableau temp dans ma feuille de rapport directement?

Probablement une faute de frappe pour le nom du tableau

même pas je pense simplement pas savoir correctement le déclarer

j'ai du reste toujours le bug (voir fichier joint)

Je ne sais pas ce que tu entends par "Déclarer"... Tu dois amener le premier tableau dans ton temp, puis écrire en VBA les formules qui permettent de récupérer les données des autres tableaux, comme je l'ai montré avec

ok je comprends mieux et ce n'est pas ce que j'avais compris à l'initial!!!


donc en une fois le tableau projet dans le tableau temp j'ajoute les recherches directement via excel via la recherche ou index equiv que je connais directement dans le document temporaire pour ensuite générer le ficier rapport final apres avoir virer les colonnes qui ne me servent pas.

voici le fichier ou j'en suis c'est à dire a temp.. donc toujours au début
test tableau.xlsm