Copy dữ liệu từ Sheet1 qua Sheet2 với điều kiện lọc, dùng VB

[cancel242]

Các bác giúp em với, em đang có vấn đề về lập trình VB với Excel. Đây là file Excel mà em đang làm thử, các bác ngó qua hộ em xem có giải pháp gì không, hiện tại em đang không biết phải làm tiếp thế nào nữa.

Em cần so sánh dữ liệu trong cột B, G và H
Điều kiện lọc như sau:
B: Lấy ra tất cả các Record trùng nhau
G: Lấy ra tất cả các Record trùng nhau
H: Tổng các Record trùng bên trên > 20
Em đã viết đc một đoạn macro nhỏ dể lấy ra tất cả các record trùng trong cột B và G sau đó đánh dầu bằng màu, nhưng để chạy tiếp một điều kiện thứ 3 là tổng các record ở cột H > 20 và colect tất cả dữ liệu đó qua Sheet2 thì chưa biết phải làm thế nào.
Đây là file ví dụ có nhúng đoạn macro em viết. các bác down về và chạy thử hộ em. cái phần miêu tả kết quả em để ở Sheet2(em lấy ra bằng tay)

 

[nghiaphuc]

Các bác giúp em với, em đang có vấn đề về lập trình VB với Excel. Đây là file Excel mà em đang làm thử, các bác ngó qua hộ em xem có giải pháp gì không, hiện tại em đang không biết phải làm tiếp thế nào nữa.

Em cần so sánh dữ liệu trong cột B, G và H
Điều kiện lọc như sau:
B: Lấy ra tất cả các Record trùng nhau
G: Lấy ra tất cả các Record trùng nhau
H: Tổng các Record trùng bên trên > 20
Em đã viết đc một đoạn macro nhỏ dể lấy ra tất cả các record trùng trong cột B và G sau đó đánh dầu bằng màu, nhưng để chạy tiếp một điều kiện thứ 3 là tổng các record ở cột H > 20 và colect tất cả dữ liệu đó qua Sheet2 thì chưa biết phải làm thế nào.
Đây là file ví dụ có nhúng đoạn macro em viết. các bác down về và chạy thử hộ em. cái phần miêu tả kết quả em để ở Sheet2(em lấy ra bằng tay)

Bạn dán công thức sau cho ô F2 của Sheet2:

=AND(COUNTIF(Sheet1!B:B,Sheet1!B2)>1,COUNTIF(Sheet1!G:G,Sheet1!G2)>1,SUMIF(Sheet1!B:B,Sheet1!B2,Sheet1!H:H)>20)

Tiếp theo, click chuột phải vào nhãn Sheet2, chọn View code và dán code sau vào:

Private Sub Worksheet_Activate()
    Sheet1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, [F1:F2], [A1:D1]
End Sub

Bây giờ, bạn chọn Sheet1, chỉnh sửa một số thông tin rồi chọn lại Sheet2 xem kết quả nhé.

 

[cancel242]

Cám ơn bác, bác có thể giải thích cho em hiểu về cách bác làm đc không. Em thử thì kết quả ok rồi.

 

[nghiaphuc]

Cám ơn bác, bác có thể giải thích cho em hiểu về cách bác làm đc không. Em thử thì kết quả ok rồi.

Thực ra thì đây chỉ là chức năng Advanced Filter trong Excel thôi và việc sử dụng AdvancedFilter trong sự kiện Worksheet_Activate của Sheet2 chỉ để nhằm mục đích lọc dữ liệu tự động, mỗi lần Sheet2 được chọn thì code sẽ chạy.
Mình giải thích một xíu về công thức tại ô F2:

=AND(COUNTIF(Sheet1!B:B,Sheet1!B2)>1,COUNTIF(Sheet1!G:G,Sheet1!G2)>1,SUMIF(Sheet1!B:B,Sheet1!B2,Sheet1!H:H)>20)

COUNTIF(Sheet1!B:B,Sheet1!B2)>1: Trả về True nếu tại cột B có sự trùng lặp dữ liệu.
COUNTIF(Sheet1!G:G,Sheet1!G2)>1: Trả về True nếu tại cột G có sự trùng lặp dữ liệu.
SUMIF(Sheet1!B:B,Sheet1!B2,Sheet1!H:H)>20: Trả về True nếu gặp những bản ghi có tổng tại cột H>20.
Như vậy công thức trên sẽ trả về True nếu gặp những bản ghi có trùng lặp tại cột B, cột G và có tổng tại cột H>20. Đây chính là yêu cầu mà bạn đã đặt ra.
Nói ngoài lề một xíu để bạn dễ hiểu code hơn: Bây giờ, bạn dán công thức trên vào ô J2 của Sheet1. Tiếp theo, chọn toàn bộ vùng dữ liệu (A1:H16), chọn Data\Filter\Advanced Filter, chọn Copy to another location và nhập:
- List range: $A$1:$H$16
- Criteria range: $J$1:$J$2
- Copy to: $A$20
Sau đó, bạn nhấn OK sẽ thấy kết quả lọc.
Bây giờ, quay trở lại code trên:

Private Sub Worksheet_Activate()
    Sheet1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, [F1:F2], [A1:D1]
End Sub

- Sheet1.[A1].CurrentRegion: Vùng dữ liệu chứa ô A1 (chính là List range ở trên).
- xlFilterCopy: Hành động lọc - Copy dữ liệu sang vùng khác, tương ứng với tùy chọn Copy to another location ở trên.
- [F1:F2]: Đây là tham số Criteria range.
- [A11]: Vùng chứa kết quả lọc, tương ứng với tham số Copy to ở trên.