Trước đây trong Podcast 2093, tôi đã chỉ ra một loại VBA đơn giản hoạt động nếu bạn không sắp xếp theo màu sắc. Hôm nay, Neeta yêu cầu VBA sắp xếp dữ liệu Excel theo màu sắc.
Điều khó nhất khi sắp xếp theo VBA là tìm ra mã màu RGB nào bạn đang sử dụng. Trong 99% trường hợp, bạn không chọn màu bằng cách nhập các giá trị RGB. Bạn đã chọn một màu bằng cách sử dụng menu thả xuống này trong Excel.
Và, trong khi bạn có thể sử dụng Fill, More Colors, Custom để biết rằng màu được chọn là RGB (112,48,160), điều đó thật phức tạp nếu bạn có nhiều màu.
Vì vậy - tôi muốn bật trình ghi macro và để trình ghi macro tìm ra mã. Mã do trình ghi macro tạo ra không bao giờ hoàn hảo. Đây là video hướng dẫn cách sử dụng trình ghi macro khi sắp xếp theo màu.
Bản ghi video
Học Excel từ Podcast, Tập 2186: Sắp xếp theo màu VBA.
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay, được gửi trên YouTube. Tôi đã có một video về cách sắp xếp với VBA và họ muốn sắp xếp theo màu sắc với VBA, điều này phức tạp hơn nhiều. Tôi nói, "Tại sao bạn không bật máy ghi macro và xem điều gì sẽ xảy ra?" Và, thật không may, máy ghi macro, bạn biết đấy, nó giúp chúng tôi đến gần nhưng nó không đưa chúng tôi đến đó.
Vì vậy, Chế độ xem, Macro, Ghi Macro, "HowToSortByColor", Lưu trữ Macro trong Sổ làm việc này-- hoàn hảo. Bấm OK. Được rồi, bây giờ trình ghi macro đang chạy, chúng ta sẽ đến đây tab Dữ liệu và chúng ta sẽ nói Sắp xếp. Chúng ta sẽ sử dụng hộp thoại Sắp xếp và chúng ta sẽ xây dựng cái này, được chứ? Vì vậy, chúng ta sẽ nói rằng chúng ta muốn thêm một cấp độ, Sắp xếp trên Cherry, nhưng không phải Sắp xếp theo Giá trị ô; chúng ta sẽ Sắp xếp theo Màu ô - Màu Ô là màu tô ở đó - và chúng ta muốn đặt màu đỏ lên trên và sau đó sao chép mức đó, và đặt thứ hai màu vàng; và sau đó chúng ta sẽ thêm một cấp độ mới-- chúng ta sẽ chuyển đến cột D, cột ngày tháng - Sắp xếp theo Màu ô, trước tiên là màu đỏ, sao chép cấp độ đó, màu vàng, sau đó ở đây; Sau đó, ở đây trong Elderberry, cột E, có một vài phông chữ màu xanh lam mà tôi không muốn thấy nó trông như thế nào,vì vậy chúng tôi sẽ thêm nó dưới dạng màu Sắp xếp theo phông chữ với màu xanh lam ở trên cùng; và sau đó nếu tất cả những thứ này đều không có màu nào cả, chúng tôi sẽ thêm một mức cuối cùng chỉ trên cột A-- Giá trị ô, Lớn nhất đến Nhỏ nhất; và bấm OK.
Được rồi, bây giờ, một vài điều-- đừng bỏ qua bước tiếp theo này - tệp của bạn, ngay bây giờ, tôi đảm bảo bạn được lưu trữ dưới dạng xlsx. Đây là thời điểm tuyệt vời để thực hiện File, Save As và lưu nó dưới dạng xlsm hoặc xlsb. Nếu bạn không làm điều đó, tất cả công việc của bạn cho đến thời điểm này sẽ bị mất khi bạn Lưu tệp này. Họ sẽ xóa macro của bất kỳ thứ gì được lưu trữ trong xlsx. Ổn thỏa?
Vì vậy, chúng tôi đã dừng ghi ở đó, và sau đó chúng tôi muốn xem các macro của mình. Vì vậy, bạn có thể thực hiện việc này với View, Macros-- View, Macros-- và tìm macro mà chúng tôi vừa ghi lại-- HowToSortByColor-- và nhấp vào Chỉnh sửa. Được rồi, đây là macro của chúng ta, và khi tôi xem xét vấn đề này, vấn đề mà chúng ta gặp phải là, hôm nay chúng ta có 25 hàng cộng với một tiêu đề. Vì vậy, nó đi xuống hàng 26. Và họ đã mã hóa cứng rằng họ sẽ luôn nhìn xuống hàng 26.
Nhưng khi tôi nghĩ về điều này, đặc biệt là so với VBA cũ để sắp xếp, chúng tôi không phải chỉ định toàn bộ phạm vi - chỉ một ô trong cột. Vì vậy, ở bất kỳ nơi nào họ có cột C26, tôi sẽ giảm nó xuống để chỉ nói, "Này, không, hãy nhìn vào ô đầu tiên trong cột đó." Vì vậy, E2, và sau đó, ở đây, A2. Vì vậy, trong trường hợp của tôi, tôi có 1, 2, 3, 4, 5, 6, cấp độ sắp xếp-- 6 điều cần thay đổi.
Và đây là phần mà trình ghi macro thực sự, thực sự tồi tệ, là chúng chỉ sắp xếp đến hàng 26 mọi lúc. Vì vậy, tôi sẽ thay đổi điều này. Tôi sẽ nói, "Nhìn này, bắt đầu ở phạm vi A21 và mở rộng nó đến .CurrentRegion." Chúng ta hãy nhìn vào Excel và xem điều đó có tác dụng gì. Vì vậy, nếu tôi chỉ chọn bất kỳ một ô nào-- A1 hoặc bất kỳ thứ gì-- và nhấn Ctrl + *, nó sẽ chọn Vùng hiện tại. Được rồi, làm đi. Ở đây, từ giữa, Ctrl + * và điều đó có nghĩa là nó mở rộng theo mọi hướng cho đến khi chạm vào mép của bảng tính, trên đầu bảng tính hoặc ở mép phải của dữ liệu hoặc mép dưới cùng của dữ liệu . Vì vậy, bằng cách nói A1 .CurrentRegion, nó giống như chuyển đến A1 và nhấn Ctrl + *. Ổn thỏa? Vì vậy, ở đây bạn phải thay đổi điều đó. Bây giờ mọi thứ khác trong macro đều ổn; nó 'tất cả sẽ làm việc. Họ có SortOnCellColor và SortOnFontColor và xlSortOn. Tôi không phải lo lắng về điều đó; tất cả những gì tôi phải làm là nhìn vào đây và thấy rằng họ đã mã hóa cứng vùng mà họ sẽ sử dụng cho phạm vi, mã hóa cứng những vùng đó đã đi được bao xa và nó không cần phải được mã hóa cứng. Và với bước đơn giản đó, thay đổi sáu mục đó và mục thứ bảy, chúng ta có một thứ sẽ hoạt động.
Bây giờ, chúng ta hãy làm bài kiểm tra. Hãy quay lại đây với Excel và chúng tôi sẽ thêm một số hàng mới ở dưới cùng. Tôi sẽ chỉ đặt số 11 ở đó, và chúng tôi sẽ thêm một vài màu Đỏ - một màu đỏ, một màu vàng, và ở đây là một màu xanh lam. Ổn thỏa. Vì vậy, nếu chúng ta chạy mã này - chạy mã này, vì vậy tôi nhấp vào đây, và nhấp vào nút Chạy - và sau đó quay lại, chúng ta sẽ thấy rằng 11 đã trở thành mục hàng đầu màu đỏ, nó hiển thị ở đó trong vàng, và nó hiển thị trong nhạc blues, vì vậy tất cả đều hoạt động hoàn hảo. Tại sao nó lên đến đỉnh? Bởi vì nó đã xảy ra rằng sắp xếp cuối cùng là Cột A và vì vậy khi có sự hòa hợp, nó sẽ xem cột A là cột phá vỡ. Vì vậy, mã đó đang hoạt động.
Để học cách viết VBA, tôi cùng với Tracy Syrstad đã viết một loạt sách, Excel VBA và MACROS. Hiện đã có một phiên bản cho các năm 2003, 2007, 2010, 2013 và 2016; sắp tới năm 2019. Được rồi, hãy đi tìm phiên bản phù hợp với phiên bản Excel của bạn và điều này sẽ giúp bạn hoàn thiện việc học.
Tóm tắt: Tập hôm nay là, Cách sử dụng VBA để sắp xếp theo màu sắc. Cách dễ nhất để làm điều này, đặc biệt là vì bạn không biết mã RGB nào được sử dụng cho từng màu - bạn chỉ chọn màu đỏ, bạn không biết mã RGB là gì và bạn không muốn đi tìm nó lên-- bật trình ghi macro bằng Chế độ xem, Macro, Ghi Macro mới. Sau khi bạn thực hiện xong việc sắp xếp, hãy nhấp vào Dừng ghi-- ở góc dưới bên trái - Alt + F8 để xem danh sách macro hoặc Chế độ xem, Macro, Xem Macro - tab Xem, Macro và sau đó Xem Macro-- điều đó thật khó hiểu. Chọn macro của bạn và nhấp vào Chỉnh sửa, và bất cứ khi nào bạn thấy C2 thành một số số trong phạm vi, chỉ cần thay đổi nó để trỏ đến hàng 2. Và sau đó, nơi họ chỉ định phạm vi để sắp xếp, Phạm vi ("A1"), CurrentRegion, sẽ mở rộng. Ổn thỏa.
Vâng, này, tôi muốn cảm ơn bạn đã ghé qua, tôi sẽ gặp lại bạn lần sau với một netcast khác của.
Trong video, tôi đã thiết lập một phân loại sáu cấp. Hộp thoại kết thúc được hiển thị ở đây:
Vào ngày tôi tình cờ ghi lại macro, tôi có 23 hàng dữ liệu cộng với một tiêu đề. Có bảy vị trí trong macro đã mã hóa số lượng hàng. Chúng phải được điều chỉnh.
Đối với mỗi cấp độ sắp xếp, có mã như sau:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Điều này thật ngớ ngẩn khi trình ghi macro chỉ định C2: C24. Bạn chỉ phải chỉ định một ô trong cột, vì vậy hãy thay đổi dòng đầu tiên ở trên thành:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Thực hiện một thay đổi tương tự cho từng cấp độ sắp xếp.
Gần cuối macro đã ghi, bạn có mã đã ghi để thực sự sắp xếp. Nó bắt đầu như thế này:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithThay vì chỉ sắp xếp A1: E24, hãy thay đổi mã bắt đầu bằng A1 và mở rộng đến vùng hiện tại. (Vùng hiện tại là những gì bạn nhận được nếu bạn nhấn Ctrl + * từ một ô).
.SetRange Range("A1").CurrentRegionMã cuối cùng được hiển thị trong video là:
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 SubGhi chú
Có khả năng sổ làm việc của bạn được lưu với phần mở rộng XLSX. Thực hiện Lưu dưới dạng để thay đổi thành tiện ích mở rộng XLSM hoặc XLSB. Mọi macro được lưu trong XLSX đều bị xóa.
Suy nghĩ của Excel trong ngày
Tôi đã hỏi những người bạn Excel Master của mình để được tư vấn về Excel. Hôm nay cần suy ngẫm:
"Một quả táo mỗi ngày giúp tránh xa VBA."
Tom Urtis
