Sebelumnya di Podcast 2093, saya menunjukkan jenis VBA sederhana yang berfungsi jika Anda tidak mengurutkan berdasarkan warna. Hari ini, Neeta meminta VBA untuk mengurutkan data Excel berdasarkan warna.
Hal tersulit tentang pengurutan menurut VBA adalah mencari tahu kode warna RGB mana yang Anda gunakan. Dalam 99% kasus, Anda tidak memilih warna dengan memasukkan nilai RGB. Anda memilih warna dengan menggunakan dropdown ini di Excel.
Dan, meskipun Anda dapat menggunakan Fill, More Colors, Custom untuk mengetahui bahwa warna yang dipilih adalah RGB (112,48,160), itu merepotkan jika Anda memiliki banyak warna.
Jadi - saya lebih suka menyalakan perekam makro dan membiarkan perekam makro mengetahui kodenya. Kode yang dihasilkan oleh perekam makro tidak pernah sempurna. Berikut adalah video yang menunjukkan cara menggunakan perekam makro saat menyortir menurut warna.
Transkrip Video
Belajar Excel dari Podcast, Episode 2186: VBA Urutkan Berdasarkan Warna.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini, dikirim di YouTube. Saya memiliki video di luar sana tentang cara mengurutkan dengan VBA, dan mereka ingin mengurutkan berdasarkan warna dengan VBA, yang jauh lebih rumit. Saya berkata, "Mengapa Anda tidak menyalakan perekam makro dan melihat apa yang terjadi?" Dan, sayangnya, perekam makro, Anda tahu, itu membuat kita dekat tetapi tidak membawa kita jauh-jauh ke sana.
Jadi Lihat, Makro, Rekam Makro, "HowToSortByColor", Simpan Makro di Buku Kerja Ini-- sempurna. Klik OK. Baiklah, jadi sekarang perekam makro sedang berjalan, kita akan menuju ke tab Data dan kita akan mengatakan Sortir. Kami akan menggunakan kotak dialog Sort dan kami akan membuat ini, oke? Jadi, kami akan mengatakan bahwa kami ingin menambahkan level, Sortir pada Cherry, tetapi bukan Sortir pada Nilai Sel; kita akan Menyortir pada Warna Sel-- Warna Sel adalah warna isian di sana-- dan kita ingin meletakkan merah di atasnya dan kemudian menyalin level itu, dan memberi kuning kedua; dan kemudian kita akan menambahkan level baru-- kita akan pergi ke kolom D, kolom tanggal-- Sortir pada Warna Sel, merah dulu, salin level itu, kuning, lalu di sini; kemudian, di sini di Elderberry, kolom E, ada beberapa font biru. Saya tidak ingin melihat seperti apa itu,jadi kita akan menambahkannya sebagai warna Sort on Font dengan warna biru di atasnya; dan kemudian jika semua ini adalah dasi tanpa warna sama sekali, kita akan menambahkan satu level terakhir hanya pada kolom A-- Cell Values, Largest to Smallest; dan klik OK.
Baiklah, sekarang, beberapa hal-- jangan lewati langkah berikutnya ini-- file Anda, sekarang, saya jamin Anda disimpan sebagai xlsx. Ini saat yang tepat untuk melakukan File, Save As, dan menyimpannya sebagai xlsm atau xlsb. Jika Anda tidak melakukannya, semua pekerjaan Anda hingga saat ini akan hilang saat Anda Menyimpan file ini. Mereka akan menghapus makro dari apa pun yang disimpan di xlsx. Baik?
Jadi kami berhenti merekam di sana, lalu kami ingin melihat makro kami. Jadi, Anda dapat melakukan ini dengan View, Macro-- View, Macros-- dan temukan makro yang baru saja kita rekam-- HowToSortByColor-- dan klik Edit. Baiklah, inilah makro kita, dan saat saya melihat ini, masalah yang kita hadapi adalah, hari ini kita kebetulan memiliki 25 baris plus sebuah heading. Jadi, turun ke baris 26. Dan mereka telah diberi kode keras bahwa mereka akan selalu melihat ke bawah ke baris 26.
Tapi saat saya memikirkan hal ini, terutama dibandingkan dengan VBA lama untuk pengurutan, kita tidak harus menentukan keseluruhan rentang - hanya satu sel di kolom. Jadi di mana pun mereka memiliki kolom C26, saya akan menguranginya menjadi hanya mengatakan, "Hei, tidak, lihat sel pertama di kolom itu." Jadi E2, lalu, di sini, A2. Jadi dalam kasus saya, saya memiliki 1, 2, 3, 4, 5, 6, level sortir-- 6 hal yang perlu diubah.
Dan ini adalah bagian yang perekam makro menjadi sangat, sangat buruk, mereka hanya akan mengurutkan ke baris 26 sepanjang waktu. Jadi saya akan mengubah ini. Saya akan berkata, "Lihat, mulai pada kisaran A21, dan perluas ke .CurrentRegion." Mari kita lihat Excel dan lihat apa fungsinya. Jadi, jika saya hanya memilih satu sel-- A1 atau apa saja-- dan tekan Ctrl + *, itu akan memilih Wilayah saat ini. Oke, ayo kita lakukan. Di sini, dari tengah, Ctrl + *, dan apa fungsinya, apakah itu meluas ke segala arah sampai menyentuh tepi spreadsheet, di atas spreadsheet, atau di tepi kanan data atau tepi bawah data . Jadi, dengan mengatakan A1 .CurrentRegion, ini seperti pergi ke A1 dan menekan Ctrl + *. Baik? Jadi, di sini Anda harus mengubah hal itu. Sekarang semua yang lain di makro baik-baik saja; saya t'Semuanya akan bekerja. Mereka mendapat SortOnCellColor dan SortOnFontColor dan xlSortOn. Saya tidak perlu khawatir tentang semua itu; yang harus saya lakukan adalah melihat ke sini dan melihat bahwa mereka meng-hardcode wilayah yang akan mereka gunakan untuk jangkauan, hard-code seberapa jauh mereka pergi, dan itu tidak perlu hard-code. Dan dengan langkah sederhana itu, mengubah enam item itu dan item ketujuh, kita memiliki sesuatu yang seharusnya berfungsi.
Sekarang, mari kita lakukan pengujian. Mari kembali ke sini ke Excel dan kami akan menambahkan beberapa baris baru di bagian bawah. Saya hanya akan menempatkan 11 di sana, dan kami akan menambahkan beberapa Merah - merah, kuning, dan kemudian di sini biru. Baik. Jadi jika kita menjalankan kode ini-- jalankan kode ini, jadi saya klik di sini, dan klik tombol Jalankan-- dan kemudian kembali, kita akan melihat bahwa 11 menjadi item teratas dengan warna merah, itu muncul di sana di kuning, dan itu muncul di blues, jadi semuanya bekerja dengan sempurna. Mengapa itu mencapai puncak? Karena kebetulan yang terakhir diurutkan adalah Kolom A dan ketika ada seri, kolom A terlihat sebagai pemecah tiebreak. Jadi kode itu berfungsi.
Untuk belajar menulis VBA, saya bersama Tracy Syrstad telah menulis serangkaian buku, Excel VBA dan MACROS. Sekarang sudah ada edisi untuk 2003, 2007, 2010, 2013 dan 2016; segera 2019. Baiklah, jadi, cari versi yang sesuai dengan versi Excel Anda dan ini akan meningkatkan kurva pembelajaran Anda.
Kesimpulan: Episode Hari Ini adalah, Bagaimana Menggunakan VBA untuk Menyortir Berdasarkan Warna. Cara termudah untuk melakukan ini, terutama karena Anda tidak tahu kode RGB apa yang digunakan untuk masing-masing warna-- Anda hanya memilih merah, Anda tidak tahu apa kode RGB itu, dan Anda tidak ingin melihatnya it up-- nyalakan perekam makro menggunakan View, Macro, Record New Macro. Setelah Anda selesai melakukan pengurutan, klik Stop Recording-- ada di pojok kiri bawah-- Alt + F8 untuk melihat daftar makro, atau Lihat, Makro, Tampilkan Makro-- tab Tampilan, Makro, dan lalu Lihat Makro-- itu membingungkan. PSPilih makro Anda dan klik Edit, dan kapan pun Anda melihat C2 ke beberapa nomor rentang, ubah saja ke menunjuk ke baris 2. Dan kemudian, di mana mereka menentukan rentang untuk diurutkan, Range ("A1"), CurrentRegion, akan meluas. Baik.
Baiklah, saya ingin mengucapkan terima kasih telah mampir, sampai jumpa di lain waktu untuk netcast lain.
Dalam video tersebut, saya menyiapkan semacam enam tingkat. Kotak dialog penutup ditampilkan di sini:
Pada hari saya kebetulan merekam makro, saya memiliki 23 baris data ditambah sebuah heading. Ada tujuh tempat di makro yang melakukan hardcode jumlah baris. Ini harus disesuaikan.
Untuk setiap tingkat pengurutan, ada kode seperti ini:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Ini konyol bahwa perekam makro menentukan C2: C24. Anda hanya perlu menentukan satu sel di kolom, jadi ubah baris pertama di atas menjadi:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Buat perubahan serupa untuk setiap tingkat pengurutan.
Menjelang akhir makro yang direkam, Anda memiliki kode yang direkam untuk benar-benar melakukan pengurutan. Ini dimulai seperti ini:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithDaripada hanya mengurutkan A1: E24, ubah kode untuk memulai di A1 dan perluas ke wilayah saat ini. (Wilayah saat ini adalah apa yang Anda dapatkan jika Anda menekan Ctrl + * dari sel).
.SetRange Range("A1").CurrentRegionKode terakhir yang ditampilkan dalam video tersebut adalah:
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 SubCatatan
Sepertinya buku kerja Anda disimpan dengan ekstensi XLSX. Lakukan Save As untuk mengubah ke ekstensi XLSM atau XLSB. Semua makro yang disimpan di XLSX akan dihapus.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Sebuah apel sehari menjauhkan VBA."
Tom Urtis
