Tự xác lập vùng dò tìm khi có dữ liệu (1 người xem)

[xuannguyen12012]

Em đang tìm hiểu về VBA để ứng dụng làm 1 vài ví dụ đơn giản. Mong các anh chị giúp em lập trình tính file này giúp em. Em xin cảm ơn.
Em có 1 bảng Bangdulieu (Bảng này có thể được coppy và paste từ 1 file excell khác vào, hoặc từ chèn dòng nhưng có trình bày giống như hiện tai), em muốn tạo ra vùng dò tìm dữ liệu và nhập Giá nhập và Giá xuất sang BangTonKho. Do vùng dữ liệu thêm vào không biết bao nhiêu dòng cả, em muốn dùng vba để xác lập vùng dò tìm tự động khi có dữ liệu ở trong Bangdulieu. Em xin gửi file kèm theo,Em xin cảm ơn!

 

[nghiaphuc]

Em đang tìm hiểu về VBA để ứng dụng làm 1 vài ví dụ đơn giản. Mong các anh chị giúp em lập trình tính file này giúp em. Em xin cảm ơn.
Em có 1 bảng Bangdulieu (Bảng này có thể được coppy và paste từ 1 file excell khác vào, hoặc từ chèn dòng nhưng có trình bày giống như hiện tai), em muốn tạo ra vùng dò tìm dữ liệu và nhập Giá nhập và Giá xuất sang BangTonKho. Do vùng dữ liệu thêm vào không biết bao nhiêu dòng cả, em muốn dùng vba để xác lập vùng dò tìm tự động khi có dữ liệu ở trong Bangdulieu. Em xin gửi file kèm theo,Em xin cảm ơn!

Chưa hiểu rõ yêu cầu của bạn, sao trong file lại có 2 sheet giống hệt nhau, vậy thì dò cái gì, từ đâu vào đâu?!
Thôi thì đoán đại, tạo một bảng dò cho bạn với name động nhé: Bạn nhấn Ctrl+F3, thêm vào 1 name có tên là BangDo và ở vị trí Refers to, bạn dán công thức sau vào:

=OFFSET(BangDulieu!$A$3,,,COUNTA(BangDulieu!$A:$A)-2,6)

Nếu cấu trúc trên sheet BangDuLieu luôn cố định như vậy (số hàng có thể thay đổi tùy ý nhưng vẫn đảm bảo tính liên tục và cấu trúc sheet) thì khi sử dụng hàm VLOOKUP để dò tìm, ở tham số thứ 2 (table_array), thay vì sử dụng địa chỉ cố định thì bạn gõ vào đó name BangDo ở trên là được, chẳng cần gì đến VBA đâu bạn.

 

[xuannguyen12012]

Chưa hiểu rõ yêu cầu của bạn, sao trong file lại có 2 sheet giống hệt nhau, vậy thì dò cái gì, từ đâu vào đâu?!
Thôi thì đoán đại, tạo một bảng dò cho bạn với name động nhé: Bạn nhấn Ctrl+F3, thêm vào 1 name có tên là BangDo và ở vị trí Refers to, bạn dán công thức sau vào:

=OFFSET(BangDulieu!$A$3,,,COUNTA(BangDulieu!$A:$A)-2,6)

Nếu cấu trúc trên sheet BangDuLieu luôn cố định như vậy (số hàng có thể thay đổi tùy ý nhưng vẫn đảm bảo tính liên tục và cấu trúc sheet) thì khi sử dụng hàm VLOOKUP để dò tìm, ở tham số thứ 2 (table_array), thay vì sử dụng địa chỉ cố định thì bạn gõ vào đó name BangDo ở trên là được, chẳng cần gì đến VBA đâu bạn.

Mong anh có thể nói rõ hơn cho em về vấn đề trên. Em gửi lại file anh xem giúp nhé. Ý là trong công thức dò tìm của em, em có chọn vùng là bảng dữ liệu, nhưng bảng Bangdulieu thì có thể thay đổi do chèn, hoặc xoá dòng do vậy, không biết được địa chỉ chính xác địa chỉ của bangdulieu la như thế nào. Do vậy, khi dùng công thức dò:
=VLOOKUP(BangTonKho!A3;BangDulieu!A2:E7;4;1) thì vùng BangDulieu!A2:E7 sẽ thay đổi. Mong anh chỉ giúp cách nào mà khi vùng Bangdulieu thay đổi nhưng công thức vẫn hiểu và lấy vùng đó. Em đang tìm hiểu thêm về VBA nên mong anh chỉ giúp em song hành giữa 2 cách trên excell và VBA với ạ. Em cảm ơn

 

[Quang_Hải]

Em tạo cái name cho vùng dữ liệu nha. Công thức dò tìm thì dò vào vùng dulieu (ten range). Khi kích hoạt sheet bangtonkho thì chạy code này. Vậy là em đã có cái name động rồi

Private Sub Worksheet_Activate()
With ThisWorkbook.Names
.Add "dulieu", RefersTo:=Sheet2.Range(Sheet2.[a2], Sheet2.[e65536].End(3))
End With
End Sub

 

[nghiaphuc]

Mong anh có thể nói rõ hơn cho em về vấn đề trên. Em gửi lại file anh xem giúp nhé. Ý là trong công thức dò tìm của em, em có chọn vùng là bảng dữ liệu, nhưng bảng Bangdulieu thì có thể thay đổi do chèn, hoặc xoá dòng do vậy, không biết được địa chỉ chính xác địa chỉ của bangdulieu la như thế nào. Do vậy, khi dùng công thức dò:
=VLOOKUP(BangTonKho!A3;BangDulieu!A2:E7;4;1) thì vùng BangDulieu!A2:E7 sẽ thay đổi. Mong anh chỉ giúp cách nào mà khi vùng Bangdulieu thay đổi nhưng công thức vẫn hiểu và lấy vùng đó. Em đang tìm hiểu thêm về VBA nên mong anh chỉ giúp em song hành giữa 2 cách trên excell và VBA với ạ. Em cảm ơn

Cách sử dụng VBA thì anh quanghai1969 đã giới thiệu rồi, mình nói thêm về cách sử dụng name động:
- Nhấn Ctrl+F3, tạo 1 name BangDo với Refers to là:

=OFFSET(BangDulieu!$A$2,,,COUNTA(BangDulieu!$A:$A)-1,5)

- Sử dụng công thức sau để dò Giá nhập và Giá xuất:

E3=VLOOKUP(A3,BangDo,4,0)
F3=VLOOKUP(A3,BangDo,5,0)

Mà thực ra thì cần gì đến name động cho nó mệt, thay vì bảng dò là vùng A2:E7, bạn cứ cho nó là $A$1:$E$10000 thì có sao đâu, chẳng hạn công thức tại E3 là:

=VLOOKUP(A3,BangDulieu!$A$1:$E$10000,4,0)

 

[nghiaphuc]

Nói thêm là công thức tại cột B của bạn sai đấy nhé, tham số thứ 2 của VLOOKUP, tốt nhất bạn nên để dưới dạng địa chỉ tuyệt đối (có thêm các dấu $ tại chỉ số hàng và chỉ số cột). Khi đó, copy công thức xuống mới không bị lỗi.
Một vấn đề nữa là trong bài toán cụ thể này thì tham số thứ 4 nên là 0 thì hợp lý hơn là 1.

 

[xuannguyen12012]

Vùng chọn là vùng có dữ liệu

Em dựa vào cách anh chỉ, dù không hiểu hoàn toàn, nhưng em cũng làm được cách là đặt tên cho bảng Bangdulieu, em chọn các cột từ A đến E và đặt tên là Bangdo. Như vậy, như bài viết đầu tiên của anh, em làm như anh chỉ " VLOOKUP để dò tìm, ở tham số thứ 2 (table_array), thay vì sử dụng địa chỉ cố định thì bạn gõ vào đó name BangDo" như vậy, khi em chèn bao nhiêu dòng vào Bangdulieu thì vùng dò vẫn được chọn. Tuy nhiên, khi chạy với số lượng lớn, file chạy chậm. Anh cho em hỏi thêm là: Có cách nào để vùng chọn là vùng có dữ liệu thôi (Không chọn nguyên cột như vậy nữa. Nghĩa là: bảng Bangdulieu có dòng dữ liệu tới đâu thì mình chọn vùng chọn tới đó thôi.

Ví dụ: Em mới nhập thêm thông tin, và vùng dữ liệu dò lúc này là : A1:E12, có cách nào chỉ chọn vùng dữ liệu hiện giờ là A1:E12 thôi, vì nếu dò đặt tên cho cả cột A đến E là Bangdo thì chương trình sẽ xử lý chậm. Em cảm ơn!

 

[nghiaphuc]

Em dựa vào cách anh chỉ, dù không hiểu hoàn toàn, nhưng em cũng làm được cách là đặt tên cho bảng Bangdulieu, em chọn các cột từ A đến E và đặt tên là Bangdo. Như vậy, như bài viết đầu tiên của anh, em làm như anh chỉ " VLOOKUP để dò tìm, ở tham số thứ 2 (table_array), thay vì sử dụng địa chỉ cố định thì bạn gõ vào đó name BangDo" như vậy, khi em chèn bao nhiêu dòng vào Bangdulieu thì vùng dò vẫn được chọn. Tuy nhiên, khi chạy với số lượng lớn, file chạy chậm. Anh cho em hỏi thêm là: Có cách nào để vùng chọn là vùng có dữ liệu thôi (Không chọn nguyên cột như vậy nữa. Nghĩa là: bảng Bangdulieu có dòng dữ liệu tới đâu thì mình chọn vùng chọn tới đó thôi.

Ví dụ: Em mới nhập thêm thông tin, và vùng dữ liệu dò lúc này là : A1:E12, có cách nào chỉ chọn vùng dữ liệu hiện giờ là A1:E12 thôi, vì nếu dò đặt tên cho cả cột A đến E là Bangdo thì chương trình sẽ xử lý chậm. Em cảm ơn!

- Cách 1: Tạo Name động thủ công (chỉ tạo 1 lần):

BangDo=OFFSET(BangDulieu!$A$2,,,COUNTA(BangDulieu!$A:$A)-1,5)

- Cách 2: Sử dụng VBA để đặt Name (sẽ tạo lại mỗi lần sheet BangDuLieu được chọn): Sửa lại một chút code của anh quanghai1969 cho phù hợp với dữ liệu thực tế:

Private Sub Worksheet_Activate()
    With ThisWorkbook.Names
        .Add "DuLieu", RefersTo:=Sheet3.[A2].Resize(Sheet3.[A65536].End(3).Row - 1, 5)
    End With
End Sub

Bạn tham khảo trong file nhé!

 

[xuannguyen12012]

Em xem trong file đã giống với điều em muốn làm rồi ạ. Anh có thể chỉ thêm cho em về ý nghĩa của các con số trong công thức không? Để em có thể tùy biến trong quá trình làm.
1. .Add "DuLieu", RefersTo:=Sheet3.[A2].Resize(Sheet3.[A65536].End(3).Row - 1, 5): Trong đó: End(3).Row-1,5 là gì?
2. BangDo=OFFSET(BangDulieu!$A$2,,,COUNTA(BangDulieu!$A:$A)-1,5): Các tham số trong công thức là như thế nào?
Em xin cảm ơn. Em đang tìm hiểu để học hỏi, mong anh thông cảm.

 

[xuannguyen12012]

Em xem trong file đã giống với điều em muốn làm rồi ạ. Anh có thể chỉ thêm cho em về ý nghĩa của các con số trong công thức không? Để em có thể tùy biến trong quá trình làm.
1. .Add "DuLieu", RefersTo:=Sheet3.[A2].Resize(Sheet3.[A65536].End(3).Row - 1, 5): Trong đó: End(3) là gì?Con số 1 và 5 thì em đã tìm hiểu được rồi.
2. BangDo=OFFSET(BangDulieu!$A$2,,,COUNTA(BangDulieu!$A:$A)-1,5): Các tham số trong công thức là như thế nào?
Em xin cảm ơn. Em đang tìm hiểu để học hỏi, mong anh thông cảm.

 

[nghiaphuc]

Em xem trong file đã giống với điều em muốn làm rồi ạ. Anh có thể chỉ thêm cho em về ý nghĩa của các con số trong công thức không? Để em có thể tùy biến trong quá trình làm.
1. .Add "DuLieu", RefersTo:=Sheet3.[A2].Resize(Sheet3.[A65536].End(3).Row - 1, 5): Trong đó: End(3).Row-1,5 là gì?
2. BangDo=OFFSET(BangDulieu!$A$2,,,COUNTA(BangDulieu!$A:$A)-1,5): Các tham số trong công thức là như thế nào?
Em xin cảm ơn. Em đang tìm hiểu để học hỏi, mong anh thông cảm.

1. Sheet3.[A65536].End(3) hay Sheet3.[A65536].End(xlUp) là ô cuối cùng trên cột A có dữ liệu. Theo cách này, bạn có thể chọn ô này bằng cách chọn ô A65536, sau đó nhấn Ctrl+Mũi tên lên. Thuộc tính Row là chỉ số hàng của 1 ô. Như vậy Sheet3.[A65536].End(3).Row là chỉ số hàng của ô cuối cùng có dữ liệu trên cột A.
Phương thức Resize(Số hàng, Số cột) dùng để điều chỉnh một vùng nào đó theo kích thước mà ta thiết lập ở trong dấu ngoặc.
Như vậy, Sheet3.[A2].Resize(Sheet3.[A65536].End(3).Row - 1, 5) trả về một vùng bắt đầu từ ô A2 trên Sheet3 (hay sheet "BangDuLieu"), mở rộng ra một vùng bằng số hàng có dữ liệu thực sự trên cột A trừ đi 1 (-1 ở đây là trừ đi hàng 1 chứa tiêu đề) và 5 cột (tức là mở rộng đến cột E).
Chính vì vậy mà mỗi lần ta nhập dữ liệu bổ sung vào sheet "BangDuLieu" một cách liên tục thì name DuLieu tự động điều chỉnh kích thước mỗi khi sheet BangDuLieu được chọn.

2. Trước hết, bạn tìm hiểu về hàm OFFSET tại topic này: Hàm Offset và Validation hoặc bài này
Cú pháp: =OFFSET(reference, rows, cols, height, width)
Trong công thức trên:
- reference là BangDulieu!$A$2
- các tham số rows và cols để trống, tức là = 0
- height = COUNTA(BangDulieu!$A:$A)-1, chính là số hàng có dữ liệu thực sự trên cột A (-1 chính là trừ hàng tiêu đề)
- width = 5, tức là 5 cột (từ A đến E)

 

[Nano]

Chào các bậc tiền bối
Mình mong được giúp đỡ việc này. Đối với mình hơn phức tạp nhưng mong mọi người cố gắng giúp đỡ.
Mình muốn lọc dữ liệu với khối dữ liệu động (sẽ thay đổi mỗi lần update về độ lớn). Điều kiện lọc là nhập 1 keyword (keyword này được hiểu có nhiều dữ liệu được định nghĩa riêng), sau đó bên dưới lọc ra list với khối dữ liệu có sẵn. Vui lòng xem file đình kèm.
Cám ơn rất nhiều

 

[Let'GâuGâu]

Chào các bậc tiền bối
Mình mong được giúp đỡ việc này. Đối với mình hơn phức tạp nhưng mong mọi người cố gắng giúp đỡ.
Mình muốn lọc dữ liệu với khối dữ liệu động (sẽ thay đổi mỗi lần update về độ lớn). Điều kiện lọc là nhập 1 keyword (keyword này được hiểu có nhiều dữ liệu được định nghĩa riêng), sau đó bên dưới lọc ra list với khối dữ liệu có sẵn. Vui lòng xem file đình kèm.
Cám ơn rất nhiều

bạn thử làm một ví dụ bằng tay vào file xem, bạn mô tả ko hiểu được
cái nhập tên line nằm ở sheet "Line define" thì ok rồi nhưng nó có dây mơ rể má gì với sheet "Matrix"???

======
ah, mà bạn thích làm bằng cthức hay vba vậy???

 

[Nano]

Mình có lấy dữ liệu bằng tay trong sheet "mau". Bạn xem file nhé
Mình muốn làm vba để cho file nhẹ bớt.

 

[Nano]

Hi All,
Mọi người cố gắng giúp mình tạo code cho file này dùm. Mình mò hoài mà chưa được.
Let'sgaugau cố gắng dùm mình. Mình đang cần gấp!
Cám ơn trước các bậc tiền bối nhé

 

[ChanhTQ@]

Chưa thể hiểu bạn muốn cái gì trong file!

Hình như bạn là người nước ngoài đang học tiếng Việt thì fải?!

 

[Let'GâuGâu]

Hi All,
Mọi người cố gắng giúp mình tạo code cho file này dùm. Mình mò hoài mà chưa được.
Let'sgaugau cố gắng dùm mình. Mình đang cần gấp!
Cám ơn trước các bậc tiền bối nhé

thử xem đúng ý bạn không?

 

[Nano]

Thanks Let'sgaugau nhiều,
Vui lòng chỉnh lại mình chút nhé.
-Ý này:
If Not Line Is Nothing Then -> bạn cho combo box có khoảng trắng để mình có thể chọn. Nghĩa là trắng là all line. Và khi chọn all line thì list ra all dữ liệu bên matrix.
-Bạn đang ràng buộc chọn ngày expiredate cho dữ liệu. Mình muốn trước tiên là xuât dữ liệu theo line bên dưới. Chọn expriredate thì xuất hiện nếu không chọn thì là tất cả các dữ liệu của line.
-Khi chọn line thì list all các chủ đề thuộc line đó. VD: Chọn line cleaner, thì dữ liệu là tất cả các chủ đề có code:
027-009
031-015
050-003
061-034
061-036
085-013
085-014
086-002
Sau đó, nếu không chọn expiredate thì dữ liệu là all học viên có code trên. nếu chọn ngày thì xhien những học viên có expireddate là ngày đó.

Cám ơn bạn,
Nano

 

[Let'GâuGâu]

1- Nghĩa là trắng là all line. Và khi chọn all line thì list ra all dữ liệu bên matrix.
2-Bạn đang ràng buộc chọn ngày expiredate cho dữ liệu. Mình muốn trước tiên là xuât dữ liệu theo line bên dưới. Chọn
"Khi chọn line thì list all các chủ đề thuộc line đó"
Nano

để nhận được sự giúp đỡ của các chuyên gia vba, bạn nên lưu ý về cách viết bài
-bài của bạn cũng ko khó, có đều bạn mô tả khá là khó hiểu
-bạn nên dùng chữ thuần việt (đừng có lẫn lộn tiếng anh vào như vây (ngoài trừ danh từ của bạn), các vị giỏi thường rất khó tính........hìhì)
=============
về yêu cầu của bạn.
bạn muốn lọc bên sheet "matrix" qua với 2 dk là line(hay topic-trong đó có số code) với ngày
- nếu mục "line" để trống nó sẻ lọc tất cả các line theo ngày?
- nếu cả line và ngày đều trống thì nó chép toàn bộ sheet "matrix" qua đây?
- nếu ngày để trống thì nó sẻ lọc toàn bộ code (có trong line đó) qua đây?
có đúng vậy ko?

 

[Nano]

Cám ơn góp ý của bạn.
Sorry đã làm nhức đầu mọi người

Đúng rồi là ý mình vậy đó bạn

Thanks
Nano

 

[mhung12005]

Cám ơn góp ý của bạn.
Sorry đã làm nhức đầu mọi người

Đúng rồi là ý mình vậy đó bạn

Thanks
Nano

Mình làm theo yêu cầu giống bạn LetGâuGâu. Bạn xem file đính kèm nhé.

 

[Nano]

Cám ơn mhung12005,
File xuất dữ liệu ra bị sai cột, nhưng không sao mình đã tự chỉnh lại ok rồi (tuy không biết nhưng cũng tự mò ra luôn)
Lần nữa cám ơn Let'sgauga va mhung12005 nhé
Nano

 

[Nano]

Hi mhung12005 va Let'gaugau,
Mình muốn thêm điều kiện nữa, giúp mình nhé
Thêm điều kiện chèn vào ô c4 của sheet1: lọc cột "current line". Lưu ý, cho mình nhập bất kỳ kí tự (keyword) nhé
Và chỉ mình VBA của điều kiện: lấy các mã chủ đề của các "line" để mình copy tương tự cho các trường hợp khác.

Thanks,
Nano

 

[Nano]

Hi All,
Có ai giúp mình phần này với!

Thanks
Nano

 

[thaibinh_excel]

- Cách 1: Tạo Name động thủ công (chỉ tạo 1 lần):

BangDo=OFFSET(BangDulieu!$A$2,,,COUNTA(BangDulieu!$A:$A)-1,5)

- Cách 2: Sử dụng VBA để đặt Name (sẽ tạo lại mỗi lần sheet BangDuLieu được chọn): Sửa lại một chút code của anh quanghai1969 cho phù hợp với dữ liệu thực tế:

Private Sub Worksheet_Activate()
    With ThisWorkbook.Names
        .Add