Anterior, în Podcast 2093, am arătat un sort VBA simplu care funcționează dacă nu sortați după culoare. Astăzi, Neeta cere VBA să sorteze datele Excel după culoare.
Cel mai dificil lucru despre sortarea în funcție de VBA este de a afla ce coduri de culoare RGB utilizați. În 99% din cazuri, nu ați ales o culoare prin introducerea valorilor RGB. Ați ales o culoare utilizând această listă derulantă în Excel.
Și, în timp ce ați putea folosi Fill, More Colours, Custom pentru a afla că culoarea selectată este RGB (112,48,160), aceasta este o problemă dacă aveți multe culori.
Deci - prefer să pornesc recorderul de macrocomenzi și să-l las pe recorderul macro să descopere codul. Codul generat de macro recorder nu este niciodată perfect. Iată videoclipul care arată cum să utilizați înregistratorul de macro atunci când sortați după culoare.
Transcriere video
Aflați Excel din Podcast, Episodul 2186: VBA Sortează după culoare.
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Întrebarea de astăzi, trimisă pe YouTube. Am avut un videoclip despre cum să sorteze cu VBA și au vrut să sorteze după culoare cu VBA, ceea ce este mult mai complicat. I-am spus: „De ce nu porniți înregistratorul macro și vedeți ce se întâmplă?” Și, din păcate, recorderul macro, știi, ne apropie, dar nu ne duce până acolo.
Așadar, Vizualizați, Macrocomenzi, Înregistrați macrocomenzile, „HowToSortByColor”, stocați macrocomenzile în acest manual - perfect. Faceți clic pe OK. Bine, așa că acum înregistratorul de macrocomenzi funcționează, vom veni aici la fila Date și vom spune Sortare. Vom folosi o casetă de dialog Sortare și o vom construi, bine? Deci, vom spune că vrem să adăugăm un nivel, Sortează pe Cireș, dar nu Sortează pe Valorile Celulei; vom sorta pe culoarea celulei - Culoarea celulei este culoarea de umplere de acolo - și vrem să punem roșu deasupra, apoi să copiem acel nivel și să punem galben în al doilea rând; și apoi vom adăuga un nou nivel - vom merge la coloana D, coloana dată - Sortează pe culoarea celulei, roșu mai întâi, copiezi acel nivel, galben și apoi aici; apoi, aici, în Elderberry, coloana E, există câteva fonturi albastre, nu vreau să văd cum arăta asta,așa că o vom adăuga ca o culoare Sortare pe font cu albastru deasupra; și apoi, dacă toate acestea sunt o cravată fără culori, vom adăuga un nivel final doar în coloana A - Valori celulare, de la cel mai mare la cel mai mic; și faceți clic pe OK.
Bine, acum, câteva lucruri - nu treceți peste următorul pas - fișierul dvs., chiar acum, vă garantez că este stocat ca xlsx. Acesta este un moment excelent pentru a face fișier, Salvați ca și salvați-l ca xlsm sau xlsb. Dacă nu faceți acest lucru, toată munca dvs. până în acest moment se va pierde atunci când salvați acest fișier. Vor șterge macrocomenzile oricărui obiect stocat în xlsx. Bine?
Așa că am oprit înregistrarea acolo și apoi vrem să ne uităm la macro-urile noastre. Deci, puteți face acest lucru cu View, Macros-- View, Macros-- și găsiți macrocomanda pe care tocmai am înregistrat - HowToSortByColor-- și faceți clic pe Editare. Bine, deci iată macro-ul nostru și, în timp ce mă uit la asta, problema pe care o avem este că astăzi avem 25 de rânduri plus un titlu. Deci, se duce la rândul 26. Și au codificat greu că vor privi întotdeauna la rândurile 26.
Dar, în timp ce mă gândesc la acest lucru, mai ales în comparație cu vechiul VBA pentru sortare, nu trebuie să specificăm întreaga gamă - doar o celulă din coloană. Deci, oriunde au coloana C26, o voi reduce în jos pentru a spune doar „Hei, nu, uită-te la prima celulă din coloana respectivă”. Deci E2, și apoi, aici, A2. Deci, în cazul meu, aveam 1, 2, 3, 4, 5, 6, niveluri de sortare - 6 lucruri de schimbat.
Și atunci aceasta este partea pe care macro recorderul o primește foarte, foarte rău, este că vor sorta doar pe rândurile 26 tot timpul. Așa că voi schimba asta. Voi spune: „Uite, începe de la intervalul A21 și extinde-l la .CurrentRegion”. Să aruncăm o privire la Excel și să vedem ce face asta. Deci, dacă aș alege o singură celulă - A1 sau ceva - și apasă Ctrl + *, se selectează regiunea curentă. Bine, să o facem. Aici, din mijloc, Ctrl + *, și ceea ce face, este că se extinde în toate direcțiile până când atinge marginea foii de calcul, deasupra foii de calcul sau la marginea dreaptă a datelor sau la marginea inferioară a datelor . Deci, spunând A1 .CurrentRegion, este ca și cum ați merge la A1 și apăsați Ctrl + *. Bine? Deci, aici trebuie să schimbi acel lucru. Acum, orice altceva din macro este în regulă; aceasta'Toți merg la muncă. Au primit SortOnCellColor și SortOnFontColor și xlSortOn. Nu trebuie să-mi fac griji pentru nimic din toate acestea; tot ce trebuie să fac este să mă uit aici și să văd că au codificat cu duritate regiunea pe care urmau să o folosească pentru gamă, au codat până unde au mers aceștia și nu trebuie să fie codificată. Și cu acel pas simplu, schimbarea celor șase elemente și a șaptelea element, avem ceva care ar trebui să funcționeze.
Acum, să facem testul. Să revenim aici la Excel și vom adăuga câteva rânduri noi în partea de jos. Voi pune doar 11s acolo și vom adăuga câteva roșii - un roșu, un galben și apoi aici un albastru. Bine. Deci, dacă vom rula acest cod - rulați acest cod, așa că fac clic aici și fac clic pe butonul Run - și apoi revin, ar trebui să vedem că acel 11 a devenit elementul de sus în roșu, a apărut acolo în galbene, și apare în blues, astfel încât totul funcționează perfect. De ce a ajuns la vârf? Deoarece s-a întâmplat ca ultimul sortare să fie Coloana A și astfel, atunci când există o egalitate, se pare că în coloana A este tiebreaker. Deci, acest cod funcționează.
Pentru a învăța cum să scriu VBA, eu, împreună cu Tracy Syrstad, am scris o serie de cărți, Excel VBA și MACROS. A existat o ediție acum pentru 2003, 2007, 2010, 2013 și 2016; în curând 2019. Bine, deci, mergeți să găsiți versiunea care se potrivește cu versiunea dvs. de Excel și astfel veți obține curba de învățare.
Rezumat: Episodul de astăzi este, Cum se folosește VBA pentru a sorta după culoare. Cel mai simplu mod de a face acest lucru, mai ales că nu știți ce coduri RGB au fost folosite pentru fiecare dintre culori - doar ați ales roșu, nu știți ce este codul RGB și nu doriți să vă uitați activează-l - pornește recorderul de macrocomenzi folosind View, Macros, Record New Macro. După ce ați terminat sortarea, faceți clic pe Opriți înregistrarea - se află în colțul din stânga jos - Alt + F8 pentru a vedea o listă de macrocomenzi sau Vizualizare, Macro, Vizualizare macro - fila Vizualizare, Macro și apoi Vizualizați macrocomenzile - este confuz. PSelectați macrocomanda și faceți clic pe Editare și, de fiecare dată când vedeți C2 la anumite numere de intervale, schimbați-l în poziția rândului 2. Apoi, unde specifică intervalul de sortat, Gama („A1”), Regiunea curentă, se va extinde. Bine.
Ei bine, vreau să-ți mulțumesc că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la.
În videoclip, am configurat un tip de șase niveluri. Caseta de dialog de final este afișată aici:
În ziua în care mi s-a întâmplat să înregistrez macro-ul, aveam 23 de rânduri de date plus un antet. Au existat șapte locuri în macro care au codificat cu greu numărul de rânduri. Acestea trebuie ajustate.
Pentru fiecare nivel de sortare, există un cod ca acesta:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Este o prostie că macro recorderul specifică C2: C24. Trebuie să specificați doar o celulă în coloană, deci schimbați prima linie de mai sus cu:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Efectuați o modificare similară pentru fiecare nivel de sortare.
Aproape de sfârșitul macro-ului înregistrat, aveți codul înregistrat pentru a face efectiv sortarea. Începe astfel:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithÎn loc să sortați doar A1: E24, schimbați codul pentru a începe în A1 și extindeți-l la regiunea curentă. (Regiunea actuală este ceea ce obțineți dacă apăsați Ctrl + * dintr-o celulă).
.SetRange Range("A1").CurrentRegionCodul final afișat în videoclip este:
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 SubNotă
Este probabil ca registrul dvs. de lucru să fie salvat cu o extensie XLSX. Faceți o Salvare ca pentru a trece la o extensie XLSM sau XLSB. Orice macrocomenzi salvate în XLSX sunt șterse.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Un Apple pe zi ține VBA departe.”
Tom Urtis
