Userform và lọc dữ liệu hiển thị lên userform

tuleanhbd · 9/12/20

Xin chào các anh chị và các bạn.
Mình có làm 1 file dữ liệu gồm nhiều cột, trong đó có cột ngày tháng, mình muốn lọc dữ liệu theo ngày tháng (có thể kèm theo điều kiện khác nữa) và hiển thị dữ liệu sau khi đã được lọc xong lên form.
Vấn đề của mình là mình đã lọc được theo điều kiện mình mong muốn, nhưng dữ liệu vẫn hiện lên nguyên cả sheet lên form không hiện riêng dữ liệu đã lọc(mình chỉ lọc trong 1 sheet không lọc qua sheet khác)
Mong nhận được sự giúp đỡ của anh chị và các bạn
Xin cảm ơn.

Mình có kèm theo file
Các bạn mở file lên, nhấn "Lọc dữ liệu" sau đó nhập số máy, điều kiện cần lọc và bấm "lọc dữ liệu" (số máy là tên các sheet)
Xin cảm ơn.

batman1 · 9/12/20

Mã:

Private Sub btLocDuLieu_Click()
...
ws.AutoFilterMode = False
ws.UsedRange.AutoFilter 1, ">=" & Me.tbDate_in.Value, xlAnd, "<=" & Me.tbDate_out.Value   
    With Me.lstBoxDuLieu
        ...
        .RowSource = ws.Name & "!A2:F" & lastrow
    End With
End Sub

Nhìn code trên chưa cần biết code có lọc chuẩn không nhưng rút cục cuối cùng lại nhập toàn bộ vùng dữ liệu vào ListBox. Thật thế, vd. số máy = 22 thì lastrow = 46. Có dữ liệu lọc hay không thì cuối cùng cũng nhập toàn bộ sheet 22 vào ListBox do có code

Mã:

.RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F46

Ngoài ra nhiều người trên GPE có thói quen rất xấu. Họ có sống và làm việc theo kiểu một mình một túp lều, một hòn đảo, không hợp tác với bất cứ ai.

Mã:

Me.tbDate_in.Value = Format("1/1/2020", "mm/dd/yyyy")
    Me.tbDate_out.Value = Format(Date, "mm/dd/yyyy")

ở trong Sub btLocDuLieu_Click

ws.UsedRange.AutoFilter 1, ">=" & Me.tbDate_in.Value, xlAnd, "<=" & Me.tbDate_out.Value

Thế này thì toi rồi. Trên máy tôi (có thể đồng nghiệp của "họ") thiết lập ngày tháng có dạng dd.mm.yyyy. Hậu quả là khi mở tập tin trên máy tôi (đồng nghiệp) thì Excel hiển thị trên sheet là vd. 13.12.2020 (ngày tháng), trong khi đó trong TextBox là vd. 12.13.2020. Lúc này lọc bằng niềm tin à? Lọc kiểu dữ liệu cần lọc là dd.mm.yyyy còn giới hạn lọc thì ở dạng mm/dd/yyyy thì có lọc muôn đời cũng không chính xác.

Thói quen bắt ngày tháng phải ở dạng cố định mm/dd/yyyy hay thậm chí dd/mm/yyyy là thói quen rất xấu. Cực kỳ xấu.

tuleanhbd · 10/12/20

batman1 đã viết:
Mã:

Private Sub btLocDuLieu_Click() ... ws.AutoFilterMode = False ws.UsedRange.AutoFilter 1, ">=" & Me.tbDate_in.Value, xlAnd, "<=" & Me.tbDate_out.Value With Me.lstBoxDuLieu ... .RowSource = ws.Name & "!A2:F" & lastrow End With End Sub

Nhìn code trên chưa cần biết code có lọc chuẩn không nhưng rút cục cuối cùng lại nhập toàn bộ vùng dữ liệu vào ListBox. Thật thế, vd. số máy = 22 thì lastrow = 46. Có dữ liệu lọc hay không thì cuối cùng cũng nhập toàn bộ sheet 22 vào ListBox do có code

Mã:

.RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F46

Ngoài ra nhiều người trên GPE có thói quen rất xấu. Họ có sống và làm việc theo kiểu một mình một túp lều, một hòn đảo, không hợp tác với bất cứ ai.

Mã:

Me.tbDate_in.Value = Format("1/1/2020", "mm/dd/yyyy") Me.tbDate_out.Value = Format(Date, "mm/dd/yyyy") ở trong Sub btLocDuLieu_Click ws.UsedRange.AutoFilter 1, ">=" & Me.tbDate_in.Value, xlAnd, "<=" & Me.tbDate_out.Value

Thế này thì toi rồi. Trên máy tôi (có thể đồng nghiệp của "họ") thiết lập ngày tháng có dạng dd.mm.yyyy. Hậu quả là khi mở tập tin trên máy tôi (đồng nghiệp) thì Excel hiển thị trên sheet là vd. 13.12.2020 (ngày tháng), trong khi đó trong TextBox là vd. 12.13.2020. Lúc này lọc bằng niềm tin à? Lọc kiểu dữ liệu cần lọc là dd.mm.yyyy còn giới hạn lọc thì ở dạng mm/dd/yyyy thì có lọc muôn đời cũng không chính xác.

Thói quen bắt ngày tháng phải ở dạng cố định mm/dd/yyyy hay thậm chí dd/mm/yyyy là thói quen rất xấu. Cực kỳ xấu.

Cám ơn bạn.
Bạn nói đúng hoàn toàn khó khăn mình đang gặp.
1.Đối với code này
.RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F46
dòng cuối cùng mình không biết làm sao để dữ liệu hiển thị động theo dữ liệu lọc, mình tìm hoài chưa ra cách làm
2.Đối với dữ liệu format theo mm/dd/yyyy thì ban đầu mình nhập ngày tháng vào thì dữ liệu ngày tháng cũng nhảy tùm lum không lọc được nên mình chọn theo cách này và mình cũng chưa biết xử lý sao đối với kiểu dữ liệu này, thật sự mình chưa biết làm chứ không phải đây là thói quen.
VBA mình chưa hiểu nhiều, mong nhận được sự giupa đỡ và hướng dẫn từ anh chị và các bạn.
Cám ơn rất nhiều ạ..

batman1 · 10/12/20

tuleanhbd đã viết:
Cám ơn bạn.
Bạn nói đúng hoàn toàn khó khăn mình đang gặp.
1.Đối với code này
.RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F46
dòng cuối cùng mình không biết làm sao để dữ liệu hiển thị động theo dữ liệu lọc, mình tìm hoài chưa ra cách làm

Vẫn sheet 22 với dòng cuối cùng có dữ liệu là 46. Giả sử sau khi lọc dòng cuối cùng nhìn thấy là 21. Hiện thời bạn xác định lastrow trước khi lọc nên bạn có lastrow = 46. Nếu bạn xác định lastrow SAU KHI LỌC thì bạn sẽ có lastrow = 21.
Nhưng nên nhớ là vẫn có .RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F21 cho dù vài dòng từ 2 tới 21 bị ẩn. Ví dụ bạn lọc các ngày >= 10.09.2020 và <= 13.09.2020 thì bạn chỉ có 12 dòng từ 10 tới 21 nhưng trong ListBox có 20 dòng từ 2 tới 21. Như vậy nguyên nhân chủ yếu là do dùng RowSource chứ không phải do khó khăn trong việc xác định lastrow. RowSource lấy cả các dòng đang bị ẩn do lọc.

2.Đối với dữ liệu format theo mm/dd/yyyy thì ban đầu mình nhập ngày tháng vào thì dữ liệu ngày tháng cũng nhảy tùm lum không lọc được nên mình chọn theo cách này và mình cũng chưa biết xử lý sao đối với kiểu dữ liệu này, thật sự mình chưa biết làm chứ không phải đây là thói quen.
VBA mình chưa hiểu nhiều, mong nhận được sự giupa đỡ và hướng dẫn từ anh chị và các bạn.

Mỗi người có phong cách riêng của mình nhưng nên làm sao cho tự nhiên. Người ngồi máy tính nhập dữ liệu ngày này qua ngày khác thì người ta biết rất rõ trên máy mình thì ngày tháng được hiển thị ở dạng nào. Vậy thì cho phép người ta nhập dữ liệu trên TextBox theo dạng y hệt như khi nhập trên sheet, cho phép người ta nhìn thấy dạng trong TextBox y hệt như dạng trên sheet thôi. Tại sao lại có kiểu hiển thị trong TextBox và bắt người ta nhập liệu trong TextBox ở dạng cứng nhắc mm/dd/yyyy?

Tôi thì cho rằng cần hiển thị trong TextBox và nhập trong TextBox theo đúng dạng như trên sheet, tức dạng được thiết lập trong system. Lúc đó thì cùng 1 code nhưng người có thiết lập trên máy dạng dd.mm.yyyy sẽ nhìn thấy vd. 17.12.2020, người có thiết lập mm/dd/yyyy sẽ nhìn thấy 12/17/2020, người có thiết lập yyyy-mm-dd sẽ nhìn thấy 2020-12-17.

Để hiển thị y như thiết lập trong system bất kể thiết lập đó như thế nào thì dùng Format(ngaythang, "Short Date")
----------
Có thể thực hiện lọc và nhập kết quả vào ListBox theo các cách, vd.:
1. Dùng AutoFilter -> copy kết quả lọc sang 1 chỗ nào đấy -> nhập từ chỗ nào đấy vào ListBox bằng RowSource (tôi không là fan của RowSource), hoặc List (tôi là fan của List).
Cách này coi như là bài tậpvề nhà cho bạn.

2. Lấy dữ liệu vào mảng -> duyệt mảng trong vòng vd. FOR để lọc -> ghi kết quả lọc vào mảng kết quả ở dạng ngược so với ListBox (các hàng liên tiếp của mảng kết quả là các cột liên tiếp của ListBox). Lý do vì với mảng động chỉ có thể tăng giảm số cột chứ không được phép tăng giảm số dòng. Do mảng kết quả "ngược" với ListBox nên ta phải nhập vào ListBox thông qua thuộc tính COLUMN.

Tôi sẽ làm theo cách 2. Ngày tháng nhập vào ListBox cũng sẽ ở dạng y như trên sheet (như thiết lập trong system). Để ghi ngày tháng đã có dạng như thiết lập trong system từ ListBox từ dòng r cột c (r và c tính từ 0) xuống sheet thì vd.

Mã:

Range("A1").Value = lstBoxDuLieu.List(r, c)

A1 sẽ là ngày tháng nhưng có thể sẽ được căn trái. Để giống như nhập bằng tay thì

Mã:

Range("A1").Value = CDate(lstBoxDuLieu.List(r, c))

Trong code sau tôi chỉ sửa Sub UserForm_Initialize và btLocDuLieu_Click. Những chỗ khác có kiểu Format(Date, "mm/dd/yyyy") thì bạn tự tìm và tự sửa.

Mã:

Private Sub UserForm_Initialize()
    tbSoMay.SetFocus
    Me.tbDate_in.Value = Format(DateSerial(2020, 1, 1), "Short Date")
    Me.tbDate_out.Value = Format(Date, "Short Date")
    With Me.lstBoxDuLieu
        .ColumnCount = 6
        .ColumnWidths = "55,90,50,200,80,50"
    End With
End Sub

Private Sub btLocDuLieu_Click()
Dim lastRow As Long, r As Long, c As Long, count As Long, dulieu(), result(), ws As Worksheet
    If Me.tbSoMay.Value = "" Then
        MsgBox "Ban Can Nhap So May", vbCritical
        Exit Sub
    End If
    For Each ws In ThisWorkbook.Worksheets
            If (ws.Name = Me.tbSoMay.Value) Then
                lastRow = ws.Range("A10000").End(xlUp).Row
                Exit For
            End If
    Next ws
    If lastRow < 2 Then
        MsgBox "Khong co so may da nhap hoac sheet khong co du lieu", vbCritical
        Exit Sub
    End If
    
    dulieu = ws.Range("A2:F" & lastRow).Value   ' cho toan bo du lieu vao mang dulieu
    For r = 1 To UBound(dulieu, 1)
        If dulieu(r, 1) >= CDate(tbDate_in.Value) And dulieu(r, 1) <= CDate(tbDate_out.Value) Then ' neu ngay thang nam trong khoang ...
            count = count + 1
            ReDim Preserve result(1 To 6, 1 To count)   ' them 1 cot trong mang ket qua result
            For c = 1 To UBound(dulieu, 2)  ' cho dong thoa dieu kien vao cot cuoi cung vua them cua mang result
                If c = 1 Then
                    result(c, UBound(result, 2)) = Format(dulieu(r, c), "Short Date")     ' dinh dang ngay thang theo dang hien duoc dung trong system
                Else
                    result(c, UBound(result, 2)) = dulieu(r, c) ' cac cot khac nhap binh thuong
                End If
            Next c
        End If
    Next r
    If count Then lstBoxDuLieu.Column = result  ' nhap mang ket qua vao ListBox dung thuoc tinh Column vi mang result xoay  90 do so voi ListBox.
End Sub

tigertiger · 10/12/20

tuleanhbd đã viết:
Cám ơn bạn.
Bạn nói đúng hoàn toàn khó khăn mình đang gặp.
1.Đối với code này
.RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F46
dòng cuối cùng mình không biết làm sao để dữ liệu hiển thị động theo dữ liệu lọc, mình tìm hoài chưa ra cách làm
2.Đối với dữ liệu format theo mm/dd/yyyy thì ban đầu mình nhập ngày tháng vào thì dữ liệu ngày tháng cũng nhảy tùm lum không lọc được nên mình chọn theo cách này và mình cũng chưa biết xử lý sao đối với kiểu dữ liệu này, thật sự mình chưa biết làm chứ không phải đây là thói quen.
VBA mình chưa hiểu nhiều, mong nhận được sự giupa đỡ và hướng dẫn từ anh chị và các bạn.
Cám ơn rất nhiều ạ..

Với dữ liệu ngày hay khác thì:
Format : định dạng chỉ là cái mặt nạ (đánh son , kẻ chỉ) ở ngoài thồi
Thực chất: nó là con số thuần túy (mặt mộc)

Nên chỉ lập trình túm cái Format thì là túm cái mặt nạ (hôm sau dùng son khác, bút kẻ màu khác là toi)

tuleanhbd · 10/12/20

tigertiger đã viết:
Với dữ liệu ngày hay khác thì:
Format : định dạng chỉ là cái mặt nạ (đánh son , kẻ chỉ) ở ngoài thồi
Thực chất: nó là con số thuần túy (mặt mộc)

Nênn chỉ lập trình túm cái Format thì là tùm cái mặt nạ (hôm sau dùng son khác, bút kẻ màu khác là toi)

Cám ơn bạn, mình chưa hiểu lắm, mình đang nghiên cứu code của bạn batman1

tuleanhbd · 10/12/20

batman1 đã viết:
Vẫn sheet 22 với dòng cuối cùng có dữ liệu là 46. Giả sử sau khi lọc dòng cuối cùng nhìn thấy là 21. Hiện thời bạn xác định lastrow trước khi lọc nên bạn có lastrow = 46. Nếu bạn xác định lastrow SAU KHI LỌC thì bạn sẽ có lastrow = 21.
Nhưng nên nhớ là vẫn có .RowSource = ws.Name & "!A2:F" & lastrow = vùng A2:F21 cho dù vài dòng từ 2 tới 21 bị ẩn. Ví dụ bạn lọc các ngày >= 10.09.2020 và <= 13.09.2020 thì bạn chỉ có 12 dòng từ 10 tới 21 nhưng trong ListBox có 20 dòng từ 2 tới 21. Như vậy nguyên nhân chủ yếu là do dùng RowSource chứ không phải do khó khăn trong việc xác định lastrow. RowSource lấy cả các dòng đang bị ẩn do lọc.

Mỗi người có phong cách riêng của mình nhưng nên làm sao cho tự nhiên. Người ngồi máy tính nhập dữ liệu ngày này qua ngày khác thì người ta biết rất rõ trên máy mình thì ngày tháng được hiển thị ở dạng nào. Vậy thì cho phép người ta nhập dữ liệu trên TextBox theo dạng y hệt như khi nhập trên sheet, cho phép người ta nhìn thấy dạng trong TextBox y hệt như dạng trên sheet thôi. Tại sao lại có kiểu hiển thị trong TextBox và bắt người ta nhập liệu trong TextBox ở dạng cứng nhắc mm/dd/yyyy?

Tôi thì cho rằng cần hiển thị trong TextBox và nhập trong TextBox theo đúng dạng như trên sheet, tức dạng được thiết lập trong system. Lúc đó thì cùng 1 code nhưng người có thiết lập trên máy dạng dd.mm.yyyy sẽ nhìn thấy vd. 17.12.2020, người có thiết lập mm/dd/yyyy sẽ nhìn thấy 12/17/2020, người có thiết lập yyyy-mm-dd sẽ nhìn thấy 2020-12-17.

Để hiển thị y như thiết lập trong system bất kể thiết lập đó như thế nào thì dùng Format(ngaythang, "Short Date")
----------
Có thể thực hiện lọc và nhập kết quả vào ListBox theo các cách, vd.:
1. Dùng AutoFilter -> copy kết quả lọc sang 1 chỗ nào đấy -> nhập từ chỗ nào đấy vào ListBox bằng RowSource (tôi không là fan của RowSource), hoặc List (tôi là fan của List).
Cách này coi như là bài tậpvề nhà cho bạn.

2. Lấy dữ liệu vào mảng -> duyệt mảng trong vòng vd. FOR để lọc -> ghi kết quả lọc vào mảng kết quả ở dạng ngược so với ListBox (các hàng liên tiếp của mảng kết quả là các cột liên tiếp của ListBox). Lý do vì với mảng động chỉ có thể tăng giảm số cột chứ không được phép tăng giảm số dòng. Do mảng kết quả "ngược" với ListBox nên ta phải nhập vào ListBox thông qua thuộc tính COLUMN.

Tôi sẽ làm theo cách 2. Ngày tháng nhập vào ListBox cũng sẽ ở dạng y như trên sheet (như thiết lập trong system). Để ghi ngày tháng đã có dạng như thiết lập trong system từ ListBox từ dòng r cột c (r và c tính từ 0) xuống sheet thì vd.

Mã:

Range("A1").Value = lstBoxDuLieu.List(r, c)

A1 sẽ là ngày tháng nhưng có thể sẽ được căn trái. Để giống như nhập bằng tay thì

Mã:

Range("A1").Value = CDate(lstBoxDuLieu.List(r, c))

Trong code sau tôi chỉ sửa Sub UserForm_Initialize và btLocDuLieu_Click. Những chỗ khác có kiểu Format(Date, "mm/dd/yyyy") thì bạn tự tìm và tự sửa.

Mã:

Private Sub UserForm_Initialize() tbSoMay.SetFocus Me.tbDate_in.Value = Format(DateSerial(2020, 1, 1), "Short Date") Me.tbDate_out.Value = Format(Date, "Short Date") With Me.lstBoxDuLieu .ColumnCount = 6 .ColumnWidths = "55,90,50,200,80,50" End With End Sub Private Sub btLocDuLieu_Click() Dim lastRow As Long, r As Long, c As Long, count As Long, dulieu(), result(), ws As Worksheet If Me.tbSoMay.Value = "" Then MsgBox "Ban Can Nhap So May", vbCritical Exit Sub End If For Each ws In ThisWorkbook.Worksheets If (ws.Name = Me.tbSoMay.Value) Then lastRow = ws.Range("A10000").End(xlUp).Row Exit For End If Next ws If lastRow < 2 Then MsgBox "Khong co so may da nhap hoac sheet khong co du lieu", vbCritical Exit Sub End If dulieu = ws.Range("A2:F" & lastRow).Value ' cho toan bo du lieu vao mang dulieu For r = 1 To UBound(dulieu, 1) If dulieu(r, 1) >= CDate(tbDate_in.Value) And dulieu(r, 1) <= CDate(tbDate_out.Value) Then ' neu ngay thang nam trong khoang ... count = count + 1 ReDim Preserve result(1 To 6, 1 To count) ' them 1 cot trong mang ket qua result For c = 1 To UBound(dulieu, 2) ' cho dong thoa dieu kien vao cot cuoi cung vua them cua mang result If c = 1 Then result(c, UBound(result, 2)) = Format(dulieu(r, c), "Short Date") ' dinh dang ngay thang theo dang hien duoc dung trong system Else result(c, UBound(result, 2)) = dulieu(r, c) ' cac cot khac nhap binh thuong End If Next c End If Next r If count Then lstBoxDuLieu.Column = result ' nhap mang ket qua vao ListBox dung thuoc tinh Column vi mang result xoay 90 do so voi ListBox. End Sub

Cám ơn bạn rất nhiều, dữ liệu tuyệt vời hơn mong đợi.
Có cách nào mình có thể thêm được columnheads không ạ.
Hôm nay mình được rất nhiều bài học hay, mình chưa hiểu về mảng bạn có bài nào trên diễn đàn nói về mảng cơ bản không cho mình xin link với.
Bài tập về nhà mình sẻ trả cho bạn sớm nhất, cám ơn bạn rất nhiều..

batman1 · 10/12/20

tuleanhbd đã viết:
Có cách nào mình có thể thêm được columnheads không ạ.

columnheads chỉ dùng được khi bạn dùng RowSource. Nếu bạn dùng List hay Column thì tự tạo Tiêu đề thôi. Trong trường hợp của bạn dùng 6 Label đặt cạnh nhau ở trên ListBox.

Hôm nay mình được rất nhiều bài học hay, mình chưa hiểu về mảng bạn có bài nào trên diễn đàn nói về mảng cơ bản không cho mình xin link với.

Bạn thử tìm trên GPE (công cụ Tìm kiếm). Có rất nhiều. Vd.

Các câu hỏi về mảng trong VBA (Array)

Xin các anh chị giúp đỡ Code Gán các giá trị của một Range là các phần tử của Mãng Ví dụ: Tôi có các giá trị của Range("A1:A10"). Tôi muốn viết code để gán giá trị của các cells từ A1:A10 là các phần tử của Mãng Arr chẳn hạn. Xin cảm ơn các anh chị

www.giaiphapexcel.com

Duyêt nhiều mảng khác nhau

Em xin chào các Sư phụ, đại ca, đại tỉ (xin mạn phép xưng em) Em có nhiều mảng cùng số cột vả khác dòng trên cùng một sheet [HTML]GI_1 = Sheet3.Range(

www.giaiphapexcel.com

tuleanhbd · 11/12/20

batman1 đã viết:
columnheads chỉ dùng được khi bạn dùng RowSource. Nếu bạn dùng List hay Column thì tự tạo Tiêu đề thôi. Trong trường hợp của bạn dùng 6 Label đặt cạnh nhau ở trên ListBox.

Bạn thử tìm trên GPE (công cụ Tìm kiếm). Có rất nhiều. Vd.

Các câu hỏi về mảng trong VBA (Array)

Xin các anh chị giúp đỡ Code Gán các giá trị của một Range là các phần tử của Mãng Ví dụ: Tôi có các giá trị của Range("A1:A10"). Tôi muốn viết code để gán giá trị của các cells từ A1:A10 là các phần tử của Mãng Arr chẳn hạn. Xin cảm ơn các anh chị

www.giaiphapexcel.com

Duyêt nhiều mảng khác nhau

Em xin chào các Sư phụ, đại ca, đại tỉ (xin mạn phép xưng em) Em có nhiều mảng cùng số cột vả khác dòng trên cùng một sheet [HTML]GI_1 = Sheet3.Range(

www.giaiphapexcel.com

cám ơn bạn rất nhiều

Userform và lọc dữ liệu hiển thị lên userform

tuleanhbd

Thành viên mới

File đính kèm

batman1

Thành viên gạo cội

tuleanhbd

Thành viên mới

batman1

Thành viên gạo cội

tigertiger

Coming back ...

tuleanhbd

Thành viên mới

tuleanhbd

Thành viên mới

batman1

Thành viên gạo cội

Các câu hỏi về mảng trong VBA (Array)

Duyêt nhiều mảng khác nhau

tuleanhbd

Thành viên mới

Các câu hỏi về mảng trong VBA (Array)

Duyêt nhiều mảng khác nhau

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng