Преди това в Podcast 2093 показах прост VBA сортиране, който работи, ако не сортирате по цвят. Днес Neeta иска VBA да сортира данните на Excel по цвят.
Най-сложното при сортирането по VBA е да разберете кои RGB цветови кодове използвате. В 99% от случаите не сте избрали цвят, като въведете RGB стойности. Избрахте цвят, като използвате това падащо меню в Excel.
И макар че бихте могли да използвате „Попълване, повече цветове, персонализирани“, за да научите, че избраният цвят е RGB (112,48 160), това е караница, ако имате много цветове.
И така - предпочитам да включа макрорекордера и да позволя на макрорекордера да разбере кода. Кодът, генериран от макрорекордера, никога не е перфектен. Ето видеото, показващо как да използвате макрорекордера при сортиране по цвят.
Видео транскрипция
Научете Excel от Podcast, епизод 2186: VBA Сортиране по цвят.
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос, изпратен в YouTube. Имах видеоклип за това как да сортирам с VBA и те искаха да сортират по цвят с VBA, което е много по-сложно. Казах, "Защо просто не включите макрорекордера и да видите какво ще стане?" И за съжаление, макрорекордерът, знаете ли, той ни приближава, но не ни стига чак там.
Така че View, Macros, Record Macro, "HowToSortByColor", Store Macro in This Workbook - перфектно. Щракнете върху OK. Добре, така че сега макрорегистраторът работи, ще дойдем тук в раздела Данни и ще кажем Сортиране. Ще използваме диалоговия прозорец Сортиране и ще изградим това, нали? И така, ще кажем, че искаме да добавим ниво, Сортиране по череша, но не и Сортиране по стойности на клетките; ще сортираме по цвят на клетката - цветът на клетката е цветът на запълване там - и искаме да поставим червено отгоре и след това да копираме това ниво и да поставим жълто на второ място и след това ще добавим ново ниво - ще преминем към колона D, колона с дата - Сортиране по цвят на клетката, първо червено, копирайте това ниво, жълто и след това тук; а след това, тук в Elderberry, колона E, има няколко сини шрифта, които не искам да виждам как изглеждаше,така че ще добавим това като цвят на Сортиране по шрифт със синьо отгоре; и тогава, ако всичко това е равенство без никакви цветове, ще добавим едно последно ниво точно в колона А - Стойности на клетките, най-големи до най-малки; и щракнете върху OK.
Добре, сега, няколко неща - не пропускайте тази следваща стъпка - вашият файл, точно сега, гарантирам, че се съхранява като xlsx. Това е чудесно време да направите File, Save As и да го запишете като xlsm или xlsb. Ако не го направите, цялата ви работа до този момент ще бъде загубена, когато запазите този файл. Те ще изтрият макросите на всичко, съхранявано в xlsx. Добре?
Затова спряхме да записваме там и след това искаме да разгледаме нашите макроси. И така, можете да направите това с View, Macros - View, Macros - и да намерите макроса, който току-що записахме - HowToSortByColor - и щракнете върху Edit. Добре, ето го нашият макрос и докато гледам това, проблемът, който имаме, е, че днес имаме 25 реда плюс заглавие. Така че той отива надолу до ред 26. И те са твърдо кодирани, че винаги ще гледат надолу към редове 26.
Но докато мисля за това, особено в сравнение със старата VBA за сортиране, не е нужно да посочваме целия диапазон - само една клетка в колоната. Така че навсякъде, където имат колона C26, ще я намаля, за да кажа просто: "Хей, не, вижте първата клетка в тази колона." Така че E2, а след това, тук, A2. Така че в моя случай имах 1, 2, 3, 4, 5, 6, нива на сортиране - 6 неща за промяна.
И тогава това е частта, която макрорегистраторът става наистина, много лош, е, че те непрекъснато ще сортират само до редове 26 Така че ще променя това. Ще кажа, "Вижте, започнете от обхват A21 и го разширете до .CurrentRegion." Нека да разгледаме Excel и да видим какво прави това. Така че, ако просто избера някоя клетка - А1 или нещо друго - и натисна Ctrl + *, тя избира текущия регион. Добре, нека го направим. Тук, от средата, Ctrl + * и това, което прави, се простира във всички посоки, докато удари ръба на електронната таблица, отгоре на електронната таблица или в десния ръб на данните или долния ръб на данните . Така че, като кажете A1 .CurrentRegion, все едно да отидете до A1 и да натиснете Ctrl + *. Добре? И така, тук трябва да промените това нещо. Сега всичко останало в макроса е наред; то'всички ще работят. Те получиха SortOnCellColor и SortOnFontColor и xlSortOn. Не трябва да се притеснявам за нищо от това; всичко, което трябва да направя, е да погледна тук и да видя, че те са кодирали региона, който ще използват за диапазона, кодирали са докъде са стигнали тези и не е необходимо да се кодират твърдо. И с тази проста стъпка, променяйки тези шест елемента и седмия елемент, имаме нещо, което трябва да работи.
Сега, нека направим теста. Нека се върнем тук в Excel и ще добавим няколко нови реда в долната част. Просто ще сложа 11-те там и ще добавим няколко червени - червено, жълто и след това тук синьо. Добре. Така че, ако продължим да изпълняваме този код - стартираме този код, така че аз щраквам тук и щраквам върху бутона Run - и след това се връщаме, трябва да видим, че този 11 стана най-горният елемент в червено, той се появи там в жълти и се появява в блуса, така че всичко работи перфектно. Защо стигна до върха? Тъй като се е случило, че последното сортиране е колона А и така, когато има равенство, изглежда колона А като тайбрек. Така че този код работи.
За да науча как да пиша VBA, аз, заедно с Трейси Сирстад, написах поредица от книги, Excel VBA и MACROS. Вече има издание за 2003, 2007, 2010, 2013 и 2016; скоро 2019. Добре, така че, отидете да намерите версията, която съответства на вашата версия на Excel и това ще ви издигне кривата на обучение.
Обобщение: Днешният епизод е, Как да използваме VBA за сортиране по цвят. Най-лесният начин да направите това, особено след като не знаете какви RGB кодове са били използвани за всеки от цветовете - току-що сте избрали червено, не знаете какъв е RGB кодът и не искате да отидете да погледнете it up - включете макрорекордера с помощта на View, Macros, Record New Macro. След като приключите със сортирането, щракнете върху Спиране на записа - това е в долния ляв ъгъл - Alt + F8, за да видите списък с макроси, или View, Macros, View Macro - раздела View, Macros и след това View Macros - това е объркващо. PS Изберете вашия макрос и щракнете върху Редактиране и по всяко време, когато видите C2 до някои номера на диапазона, просто го променете, за да посочите към ред 2. И тогава, когато те определят диапазона за сортиране, Range ("A1"), CurrentRegion, ще се разшири. Добре.
Е, хей, искам да ви благодаря, че отбихте, ще се видим следващия път за поредното излъчване от.
Във видеото настроих сортиране на шест нива. Крайният диалогов прозорец е показан тук:
В деня, в който случайно записах макроса, имах 23 реда данни плюс заглавие. Имаше седем места в макроса, които трудно кодираха броя на редовете. Те трябва да бъдат коригирани.
За всяко ниво на сортиране има код като този:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Това е глупаво, че макрорекордерът посочва C2: C24. Трябва да посочите само една клетка в колоната, така че променете първия ред по-горе на:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Направете подобна промяна за всяко от нивата на сортиране.
Близо до края на записания макрос имате записания код, който всъщност да извърши сортирането. Започва така:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithВместо да сортирате само A1: E24, променете кода, за да започне в A1 и да се разшири до текущия регион. (Текущият регион е това, което получавате, ако натиснете Ctrl + * от клетка).
.SetRange Range("A1").CurrentRegionПоследният код, показан във видеото, е:
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 SubЗабележка
Вероятно вашата работна книга е запазена с разширение XLSX. Направете Save As, за да преминете към XLSM или XLSB разширение. Всички макроси, запазени в XLSX, се изтриват.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Един Apple на ден държи VBA далеч.“
Том Уртис
