Auparavant, dans Podcast 2093, j'ai montré un tri VBA simple qui fonctionne si vous ne triez pas par couleur. Aujourd'hui, Neeta demande au VBA de trier les données Excel par couleur.
La chose la plus délicate à propos du tri par VBA est de déterminer les codes de couleur RVB que vous utilisez. Dans 99% des cas, vous n'avez pas choisi de couleur en entrant des valeurs RVB. Vous avez choisi une couleur en utilisant cette liste déroulante dans Excel.
Et, bien que vous puissiez utiliser Remplissage, Plus de couleurs, Personnalisé pour apprendre que la couleur sélectionnée est RVB (112,48,160), c'est un problème si vous avez beaucoup de couleurs.
Donc - je préfère allumer l'enregistreur de macro et laisser l'enregistreur de macro comprendre le code. Le code généré par l'enregistreur de macros n'est jamais parfait. Voici la vidéo montrant comment utiliser l'enregistreur de macros lors du tri par couleur.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2186: VBA Trier par couleur.
Salut, bon retour sur le netcast, je suis Bill Jelen. Question du jour, envoyée sur YouTube. J'avais une vidéo sur la façon de trier avec VBA, et ils voulaient trier par couleur avec VBA, ce qui est beaucoup plus compliqué. J'ai dit: "Pourquoi ne pas allumer simplement l'enregistreur de macros et voir ce qui se passe?" Et, malheureusement, l'enregistreur de macros, vous savez, cela nous rapproche mais cela ne nous amène pas tout le chemin.
Donc, afficher, macros, enregistrer une macro, "HowToSortByColor", stocker une macro dans ce classeur - parfait. Cliquez sur OK. D'accord, alors maintenant que l'enregistreur de macros est en cours d'exécution, nous allons venir ici dans l'onglet Données et nous allons dire Trier. Nous allons utiliser une boîte de dialogue Trier et nous allons construire ceci, d'accord? Donc, nous allons dire que nous voulons ajouter un niveau, Trier sur Cherry, mais pas Trier sur les valeurs de cellule; nous allons trier sur la couleur de la cellule - la couleur de la cellule est la couleur de remplissage - et nous voulons mettre du rouge en haut, puis copier ce niveau, et mettre le jaune en second; puis nous ajouterons un nouveau niveau - nous allons passer à la colonne D, la colonne de date - Trier sur la couleur de la cellule, rouge d'abord, copiez ce niveau, jaune, puis ici; et puis, ici dans Sureau, colonne E, il y a quelques polices bleues, je ne veux pas voir à quoi cela ressemblait,nous allons donc ajouter cela comme une couleur de tri sur la police avec du bleu en haut; et puis si tout cela est une égalité pour aucune couleur du tout, nous ajouterons un dernier niveau juste sur la colonne A - Valeurs de cellule, du plus grand au plus petit; et cliquez sur OK.
Très bien, maintenant, quelques choses - ne sautez pas cette étape suivante - votre fichier, maintenant, je vous garantis qu'il est stocké au format xlsx. C'est le moment idéal pour faire un fichier, enregistrer sous et l'enregistrer sous xlsm ou xlsb. Si vous ne le faites pas, tout votre travail jusqu'à présent sera perdu lorsque vous enregistrerez ce fichier. Ils supprimeront les macros de tout ce qui est stocké dans xlsx. Bien?
On a donc arrêté d'enregistrer là-bas, puis on a envie d'aller regarder nos macros. Ainsi, vous pouvez le faire avec View, Macros - View, Macros - et trouver la macro que nous venons d'enregistrer - HowToSortByColor - et cliquer sur Edit. Très bien, voici donc notre macro, et quand je regarde cela, le problème que nous avons est qu'aujourd'hui nous avons 25 lignes plus un en-tête. Cela va donc à la ligne 26. Et ils ont codé en dur qu'ils vont toujours regarder vers le bas aux lignes 26.
Mais en y réfléchissant, surtout par rapport à l'ancien VBA pour le tri, nous n'avons pas à spécifier toute la plage - juste une cellule dans la colonne. Donc, partout où ils ont la colonne C26, je vais la réduire pour dire simplement: "Hé, non, regardez la première cellule de cette colonne." Donc E2, et puis, ici, A2. Donc, dans mon cas, j'avais 1, 2, 3, 4, 5, 6, niveaux de tri - 6 choses à changer.
Et puis c'est la partie que l'enregistreur de macros devient vraiment, vraiment mauvais, c'est qu'ils vont seulement trier les lignes 26 tout le temps. Alors je vais changer ça. Je vais dire: "Regardez, commencez à la plage A21 et étendez-la à .CurrentRegion." Jetons un coup d'œil à Excel et voyons ce que cela fait. Donc, si je voulais simplement choisir une cellule - A1 ou autre - et appuyer sur Ctrl + *, cela sélectionne la région actuelle. Ok, faisons-le. Ici, à partir du milieu, Ctrl + *, et ce que cela fait, c'est qu'il s'étend dans toutes les directions jusqu'à ce qu'il atteigne le bord de la feuille de calcul, en haut de la feuille de calcul, ou sur le bord droit des données ou le bord inférieur des données . Donc, en disant A1 .CurrentRegion, c'est comme aller à A1 et appuyer sur Ctrl + *. Bien? Donc, ici, vous devez changer cette chose. Maintenant, tout le reste de la macro va bien; il'tout va fonctionner. Ils ont obtenu SortOnCellColor et SortOnFontColor et xlSortOn. Je n'ai pas à m'inquiéter de tout cela; tout ce que j'ai à faire est de regarder ici et de voir qu'ils ont codé en dur la région qu'ils allaient utiliser pour la plage, codé en dur jusqu'où ils sont allés, et il n'est pas nécessaire de le coder en dur. Et avec cette simple étape, changer ces six éléments et le septième élément, nous avons quelque chose qui devrait fonctionner.
Maintenant, faisons le test. Revenons ici à Excel et nous ajouterons quelques nouvelles lignes en bas. Je vais juste mettre 11 ici, et nous ajouterons quelques rouges - un rouge, un jaune, puis ici un bleu. Bien. Donc, si nous allons exécuter ce code - exécutez ce code, donc je clique ici, et cliquez sur le bouton Exécuter - et puis revenons, nous devrions voir que ce 11 est devenu le premier élément en rouge, il est apparu là-bas dans le jaunes, et cela apparaît dans les bleus, donc tout fonctionne parfaitement. Pourquoi est-il allé au sommet? Parce qu'il est arrivé que le dernier tri soit la colonne A et donc, quand il y a une égalité, il regarde la colonne A comme le départage. Donc, ce code fonctionne.
Pour apprendre à écrire VBA, j'ai écrit avec Tracy Syrstad une série de livres, Excel VBA et MACROS. Il y a eu une édition maintenant pour 2003, 2007, 2010, 2013 et 2016; bientôt 2019. Très bien, alors, allez trouver la version qui correspond à votre version d'Excel et cela vous permettra de progresser dans la courbe d'apprentissage.
Récapitulation: l'épisode d'aujourd'hui est, Comment utiliser VBA pour trier par couleur. Le moyen le plus simple de le faire, d'autant plus que vous ne savez pas quels codes RVB ont été utilisés pour chacune des couleurs - vous venez de choisir le rouge, vous ne savez pas ce qu'est le code RVB, et vous ne voulez pas aller chercher it up - allumez l'enregistreur de macros en utilisant View, Macros, Record New Macro. Une fois que vous avez terminé le tri, cliquez sur Arrêter l'enregistrement - c'est dans le coin inférieur gauche - Alt + F8 pour voir une liste de macros, ou Afficher, Macros, Afficher macro - l'onglet Affichage, Macros et puis Afficher les macros - c'est déroutant. PSélectionnez votre macro et cliquez sur Modifier, et à chaque fois que vous voyez C2 avec des numéros de plage, changez-le simplement pour qu'il pointe sur la ligne 2. Et puis, là où ils spécifient la plage à trier, Range ("A1"), CurrentRegion, s'étendra. Bien.
Eh bien, je tiens à vous remercier d'être passé, je vous verrai la prochaine fois pour une autre diffusion sur le net.
Dans la vidéo, j'ai mis en place un tri à six niveaux. La boîte de dialogue de fin est affichée ici:
Le jour où j'ai enregistré la macro, j'avais 23 lignes de données plus un en-tête. Il y avait sept endroits dans la macro qui codaient en dur le nombre de lignes. Celles-ci doivent être ajustées.
Pour chaque niveau de tri, il existe un code comme celui-ci:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) C'est idiot que l'enregistreur de macros spécifie C2: C24. Vous n'avez qu'à spécifier une cellule dans la colonne, alors remplacez la première ligne ci-dessus par:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Effectuez une modification similaire pour chacun des niveaux de tri.
Vers la fin de la macro enregistrée, vous disposez du code enregistré pour effectuer le tri. Cela commence comme ceci:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithPlutôt que de trier uniquement A1: E24, modifiez le code pour qu'il commence par A1 et étende à la région actuelle. (La région actuelle est ce que vous obtenez si vous appuyez sur Ctrl + * à partir d'une cellule).
.SetRange Range("A1").CurrentRegionLe code final montré dans la vidéo est:
Sub HowToSortByColor() HowToSortByColor Macro ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear ' Sort column C by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort Column C by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column D by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort column D by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column E by blue font ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("E2"), _ xlSortOnFontColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(0, 176, 240) ' Sort Column A by Values descending ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal ' Perform the Sort With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1").CurrentRegion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End SubRemarque
Il est probable que votre classeur soit enregistré avec une extension XLSX. Effectuez un Enregistrer sous pour passer à une extension XLSM ou XLSB. Toutes les macros enregistrées dans XLSX sont supprimées.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Un Apple par jour éloigne le VBA."
Tom Urtis
