Bài toán đặt ra là với khối lượng dữ liệu lớn, mình phải làm thế nào để tìm thấy dữ liệu cần thiết một cách đầy đủ và trực quan. Nếu phải làm theo cách thủ công sẽ mất rất nhiều thời gian, tốn công sức. Để giải quyết vấn đề này, chúng ta hoặc là phải sử dụng sức mạnh công cụ có sẵn trong Excel hoặc là phải viết các Macro bằng ngôn ngữ VBA trong Excel.
Trong bài này, mình sẽ chia sẻ ba cách khác nhau, phân tích với bạn cách để có thể tự tạo cách tìm kiếm trong VBA và cách để có thể tùy biến tìm kiếm cho File Excel của riêng bạn.
Cách 1: Tạo Hộp Tìm kiếm trong Excel bằng hộp văn bản.
Đầu tiên, hãy chuyển đến tab Developer (Nhà phát triển) > chọn Insert (Chèn) > Tại đây, một trình đơn thả xuống sẽ xuất hiện > chọn Textbox từ ActiveX Controls.
Sau đó, nhấp và kéo con trỏ chuột vào nơi bạn muốn Hộp văn bản của mình. Sau đó, Click chuột phải vào Text Box và chọn Thuộc tính.
Tiếp theo, chọn một ô trong bảng tính trống dưới dạng LinkedCell . Ở đây, tôi chọn ô B2; Đặt tên cho hộp văn bản là txtsearch giống như hình ảnh bên dưới.
Bây giờ, nhấp đúp vào Hộp văn bản. Tiếp theo, viết đoạn mã sau:
Private Sub Txtsearch_Change()
Dim mRng as Range
Dim lrow as long
Application.ScreenUpdating = False
Sheet1.AutofilterMode=false
Lrow=sheet1.usedRange.Rows.Count
Set mRng = Sheet1.Range(“A4:D” & lrow)
If Trim(txtsearch.text) <> vbnullstring then
mRng.AutoFilter field:=2, Criteria1:= "*" & [B2] & "*", Operator:=xlFilterValues
End if
Set mRng = nothing
Application.ScreenUpdating = True
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi Hộp văn bản. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Tiếp theo sử dụng từ khóa Dim để khai báo các biến.
Đặt thuộc tính Sheet1.AutofilterMode = false để tắt chế độ autofilter trong Sheet1.
Sau đó, tôi đã lấy dòng cuối của bảng trong Sheet1 mà có dữ liệu và lưu vào biến lrow.
Sử dụng từ khóa set để thiết lập vùng cần lọc và lưu vào biến mRng.
Sử dụng hàm if để kiểm tra xem đã có sự thay đổi tại ô nhập liệu txtsearch hay chưa ?
Tiếp theo, tôi sử dụng phương thức mRng.AutoFilter để lọc phạm vi. Ở đây, tôi đã chọn 2 cho trường sẽ lọc cột thứ 2 của bảng và tôi đã chọn “*” & [B2] & “*” làm Tiêu chí sẽ khớp chữ cái của các ô trong cột 2 (cột tên) so với thông tin nhập trong Hộp tìm kiếm. Đối với Operator, tôi đã chọn xlFilterValues sẽ lọc các giá trị.
Giải phóng biến đối tượng mRng.
Sau đó, tôi đặt thuộc tính Application.ScreenUpdating là True vì tôi đã đến phần cuối cùng của macro.
Cuối cùng, tôi đã kết thúc Thủ tục.
Bây giờ, hãy lưu mã và quay lại trang tính. Sau đó, bạn có thể tìm kiếm trong Hộp tìm kiếm bất cứ điều gì bạn muốn. Ở đây, tôi đã tìm kiếm “h” và những cái tên có chứa chữ “h” đã xuất hiện.
Cách 2: Sử dụng Text Box với các nút tùy chọn.
Trong ví dụ này, tôi sẽ tạo Hộp tìm kiếm trong Excel bằng VBA với hai Nút Option để tìm kiếm cho 2 trường hợp khác nhau. Một cái sẽ lọc nếu giá trị chứa chữ cái được tìm kiếm và cái kia sẽ lọc nếu chữ cái được tìm kiếm khớp với chữ cái đầu tiên của giá trị.
Các bước thực hiện:
Để bắt đầu, hãy chèn một Hộp văn bản và liên kết nó với một ô, giống như ở trên.
Chèn Option Button từ ActiveX Controls.
Sau đó, nhấp và kéo con trỏ chuột vào nơi bạn muốn có Nút Option.
Bây giờ, bạn sẽ thấy một Nút Option được chèn vào trang tính Excel của bạn.
Sau đó, chèn một Nút Option khác theo cách tương tự.
Ở đây, mình sẽ thay đổi tiêu đề của Option Button.
Đầu tiên, Nhấp chuột phải vào Nút Option. Sau đó, chọn Thuộc tính. viết Caption như bạn muốn. Ở đây, tôi đổi thành “Chữ cái đầu”. Sau đó, thay đổi Tiêu đề của Nút Option khác theo cách tương tự. Ở đây, tôi đổi thành “Chứa chữ cái”.
Đặt name của OptionButton1 là Opt1 và OptionButton2 là Opt2.
Bây giờ, nhấp đúp vào Hộp văn bản. Tiếp theo, viết đoạn mã sau:
Private Sub Txtsearch_Change()
Dim mRng As Range
Dim lrow As Long
Application.ScreenUpdating = False
Sheet1.AutoFilterMode = False
lrow = Sheet1.UsedRange.Rows.Count
Set mRng = Range("A4:D" & lrow)
If (Trim(Txtsearch.Text) <> vbNullString) And Opt1 Then
mRng.AutoFilter field:=2, Criteria1:=[B2] & "*", Operator:=xlFilterValues
ElseIf (Trim(Txtsearch.Text) <> vbNullString) And Opt2 Then
mRng.AutoFilter field:=2, Criteria1:="*" & [B2] & "*", Operator:=xlFilterValues
End If
Set mRng = Nothing
Application.ScreenUpdating = True
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi Hộp văn bản. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã sử dụng Câu lệnh IF để đưa ra các kết quả khác nhau trong hai tình huống khác nhau. Nếu Opt1 được chọn và có sự thay đổi trong ô nhập liệu Txtsearch thì Criteria1 được chọn là [B2] & “*” cho phương thức Range.AutoFilter. Điều này có nghĩa là Nó sẽ lọc các giá trị bắt đầu bằng chữ cái được tìm kiếm. Ngược lại, Tiêu chí được chọn là “*” & [B2] & “*” cho phương thức Range.AutoFilter. Điều đó có nghĩa là Nó sẽ lọc các giá trị có chứa chữ cái được tìm kiếm.
Sau đó, tôi kết thúc câu lệnh IF.
Cuối cùng, tôi đã kết thúc Thủ tục.
Bây giờ, hãy lưu mã và quay lại trang tính của bạn.
Sau đó, nhấp đúp chuột vào Option Button. hãy viết đoạn mã sau:
Private Sub Opt1_Click()
Call TxtSearch_Change
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi Opt1. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã sử dụng câu lệnh Call để gọi Thủ tục có tên TxtSearch_Change.
Sau đó, tôi kết thúc Thủ tục.
Bây giờ, hãy lưu mã và quay lại trang tính của bạn một lần nữa.
Tiếp theo, nhấp đúp chuột vào Option Button có tiêu đề “Chứa chữ cái”. Sau đó, viết đoạn mã sau:
Private Sub Opt2_Click()
Call TxtSearch_Change
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi Opt2. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã sử dụng câu lệnh Call để gọi Thủ tục có tên TxtSearch_Change.
Sau đó, tôi kết thúc Thủ tục.
Bây giờ, hãy lưu mã và quay lại trang tính của bạn một lần nữa.
Giờ đây, bạn có thể tìm kiếm trong Hộp Tìm kiếm bất kỳ thứ gì bạn muốn và chọn Nút Option ưa thích của mình. Trong hình ảnh sau đây, bạn có thể thấy rằng tôi hiện đã chọn Nút tùy chọn có tên “Chứa chữ cái” và các tên có chứa chữ “a” được lọc.
Cách 3: Sử dụng VBA UserForm để tạo hộp tìm kiếm.
Ở đây, tôi sẽ giải thích một trường hợp khác. Để giải thích trường hợp này, tôi đã lấy tập dữ liệu sau đây không phải là bảng mà là một dải dữ liệu. Đối với trường hợp này, tôi sử dụng UserForm.
Trong bước đầu tiên này, tôi sẽ tạo UserForm và sửa đổi nó theo nhu cầu của tôi.
Đầu tiên, hãy chuyển đến tab Developer (Nhà phát triển) > chọn Trình soạn thảo Visual Basic > nhấp vào menu thả xuống cho UserForm > chọn UserForm.
Bây giờ, một UserForm sẽ xuất hiện. Nhấp chuột phải vào UserForm > chọn Thuộc tính.
Bây giờ, Thuộc tính cho UserForm1 sẽ xuất hiện ở bên trái màn hình. Ở đây, tôi sẽ thay đổi BackColor của UserForm, chọn màu bạn muốn cho màu nền của bạn.
Thay đổi thuộc tính Name (tên) cho UserForm1 là Frm_Search.
Thay đổi Caption nếu bạn muốn. Ở đây, tôi đã thay đổi của tôi thành Tim_Kiem_Frm.
Thay đổi Height và Width của UserForm.
Sau đó, chọn Nhãn từ Hộp công cụ. Tiếp theo, nhấp và kéo con trỏ chuột của bạn đến nơi bạn muốn Nhãn xuất hiện. thay đổi Caption từ Properties . Ở đây, tôi đổi nó thành “Chọn trường”. Đặt thuộc tính name là lbl1.
Tiếp theo, chọn ComboBox từ Hộp công cụ. Sau đó, nhấp và kéo con trỏ chuột đến nơi bạn muốn ComboBox của mình. Đổi thuộc tính name thành Cbo1.
Bây giờ, chọn Nhãn từ Hộp công cụ một lần nữa. Sau đó, nhấp và kéo con trỏ chuột của bạn đến nơi bạn muốn Nhãn. thay đổi Caption của Label2 theo cách tương tự. Ở đây, tôi đã thay đổi thành “Tìm kiếm:” và đổi thuộc tính name là lbl2.
Tại đây, chọn TextBox từ Toolbox. Sau đó, nhấp và kéo con trỏ chuột vào nơi bạn muốn TextBox. Đổi thuộc tính name thành TxtSearch.
Sau đó, chọn ListBox từ Hộp công cụ. Tiếp theo, nhấp và kéo con trỏ chuột đến nơi bạn muốn ListBox. Đổi thuộc tính name thành Lst_DS.
Chọn một nút lệnh CommandButton từ Toolbox dùng để đóng UserForm.
Sau đó, nhấp và kéo con trỏ chuột đến nơi bạn muốn CommandButton.
Bây giờ, bạn sẽ thấy CommandButton được chèn vào UserForm.
Đầu tiên, Nhấp chuột phải vào CommandButton > chọn Thuộc tính > Sau đó, thay đổi Caption thành “Thoát”. Đặt Name của nút là CmdThoat.
Cuối cùng, tôi đã chèn mọi thứ tôi muốn vào UserForm của mình.
Trong bước này, tôi sẽ viết mã VBA để tạo Hộp tìm kiếm trong Excel bằng cách sử dụng UserForm.
Đầu tiên, nhấp đúp vào bất kỳ đâu trong UserForm.
Sau đó, viết đoạn mã sau vào UserForm.
Private Sub UserForm_Initialize()
Dim i As Integer
For i = 2 To 4
Me.Cbo1.AddItem ActiveSheet.Cells(3, i).Value
Next
With Me.Lst_DS
.ColumnCount = 3
.ColumnWidths = "90;90;90"
End With
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi UserForm. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã khai báo một biến có tên i là Số nguyên.
Sau đó, tôi sử dụng Vòng lặp For...Next để duyệt qua các cột từ 2 đến 4.
Tiếp theo, tôi sử dụng từ khóa Me để biến nó hoạt động như một biến được khai báo ngầm cho UserForm. Sau đó, tôi đã sử dụng phương thức Cbo1.AddItem để thêm các tiêu đề của bảng vào ComboBox.
Sau đó, tôi đã sử dụng Câu lệnh With để xác định ColumnCount và ColumnWidths trong Lst_DS.
Sau đó, tôi đã kết thúc câu lệnh With.
Cuối cùng, tôi đã kết thúc Thủ tục.
Sau đó, lưu mã và quay lại UserForm.
Bây giờ, nhấp đúp vào ComboBox.
Sau đó, viết đoạn mã sau vào UserForm.
Dim Ten_Truong
Private Sub Cbo1_Change()
Dim i As Integer
Dim TD_cot
TD_cot = Array("B", "C", "D")
For i = 2 To 4
If ActiveSheet.Cells(3, i).Value = Me.Cbo1.Value Then
Ten_Truong = TD_cot(i - 2)
End If
Next
Me.Lst_DS.Clear
Me.TxtSearch.Value = ""
Me.TxtSearch.SetFocus
End Sub
Phân tích mã:
Ở đây, tôi đã khai báo một biến tên là Ten_Truong.
Tiếp theo, một Thủ tục riêng tư đã được tạo bởi UserForm. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã khai báo một biến có tên i là Số nguyên.
Sau đó, tôi đã khai báo một biến khác có tên là TD_cot.
Tiếp theo, tôi đã sử dụng hàm Array để gán một mảng là TD_cot.
Sau đó, tôi sử dụng Vòng lặp For...Next để duyệt qua các cột từ 2 đến 4.
Sau đó, tôi đã sử dụng câu lệnh IF để kiểm tra xem giá trị trong ô có khớp với giá trị trong Cbo1 hay không. Nếu nó khớp thì câu lệnh sẽ trả về trường dưới dạng TD_cot(i – 2) đây là cột sẽ được lọc.
Tiếp theo, tôi đã kết thúc Câu lệnh IF.
Sau đó, tôi đã sử dụng phương thức Lst_DS.Clear để xóa tất cả dữ liệu khỏi Lst_DS. Ở đây, tôi đã sử dụng từ khóa Me để làm cho nó hoạt động giống như một biến được khai báo ngầm.
Sau đó, tôi đã sử dụng TxtSearch.Value để chỉ định văn bản trong hộp văn bản. Và đặt nó là trống. Tôi cũng đã sử dụng từ khóa Me để làm cho nó hoạt động giống như một biến được khai báo ngầm.
Sau đó, tôi đã sử dụng phương thức TxtSearch.SetFocus để di chuyển tiêu điểm đến biểu mẫu được chỉ định này.
Cuối cùng, tôi đã kết thúc Thủ tục.
Sau đó, lưu mã và quay lại UserForm.
Sau đó, nhấp đúp vào CmdThoat.
Bây giờ, hãy viết đoạn mã sau vào UserForm.
Private Sub CmdThoat_Click()
Unload Me
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi UserForm. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã sử dụng câu lệnh Unload Me để đóng UserForm lại.
Cuối cùng, tôi đã kết thúc Thủ tục.
Bây giờ, hãy lưu mã và quay lại UserForm.
Sau đó, nhấp đúp vào TextBox.
Bây giờ, hãy viết đoạn mã sau vào UserForm.
Private Sub TxtSearch_Change()
Dim k As Integer
Dim lrow As Integer
Dim str as string
On Error Resume Next
If Me.TxtSearch.Text = "" Then
Me.Lst_DS.Clear
Exit Sub
End If
Me.Lst_DS.Clear
lrow = ActiveSheet.Range("B10000").End(xlUp).Row
For k = 5 To lrow
str = Len(Me.TxtSearch.Text)
If UCase(Left(ActiveSheet.Cells(k, Ten_Truong).Value, str)) = UCase(Me.TxtSearch.Text) Then
With Me.Lst_DS
.AddItem ActiveSheet.Cells(k, "B").Value
.List(.ListCount - 1, 1) = ActiveSheet.Cells(k, "C").Value
.List(.ListCount - 1, 2) = ActiveSheet.Cells(k, "D").Value
End With
End If
Next
End Sub
Tại đây, một Thủ tục riêng tư đã được tạo bởi UserForm. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó mình viết On Error Resume Next để tiếp tục mã nếu có lỗi xảy ra.
Sau đó, tôi đã sử dụng câu lệnh IF để kiểm tra xem TextBox có trống không. Nếu trống thì Lst_DS sẽ bị xóa.
Sau đó, tôi kết thúc câu lệnh IF.
Sau đó, tôi đã khai báo một biến có tên k là Integer và một biến khác có tên lrow là Integer.
Tiếp theo, tôi đã sử dụng thuộc tính Range.End(xlUp) để tìm số hàng không trống cuối cùng.
Sau đó, tôi đã sử dụng Vòng lặp For...Next để duyệt qua tất cả các hàng trong một cột.
Sau đó, tôi sử dụng một câu lệnh IF khác để xem văn bản trong TextBox có khớp với giá trị trong ô hay không. Ở đây, tôi đã sử dụng chức năng Left để khớp từ đầu văn bản. Tôi cũng đã sử dụng chức năng UCase để chuyển đổi chữ cái thành chữ in hoa.
Tiếp theo, tôi đã sử dụng câu lệnh With để thêm mục vào ListBox và tạo danh sách với 2 cột còn lại, nếu hai giá trị khớp nhau.
Sau đó, tôi kết thúc câu lệnh With.
Sau đó, tôi kết thúc câu lệnh IF.
Cuối cùng, tôi đã kết thúc Thủ tục.
Bây giờ, hãy lưu mã và quay lại trang tính của bạn.
Tiếp theo, chèn một Nút lệnh giúp hiển thị UserForm.
Đầu tiên, hãy chuyển đến tab Developer (Nhà phát triển) > chọn Insert (Chèn). Tại đây, một trình đơn thả xuống sẽ xuất hiện > chọn CommandButton từ ActiveX Controls.
Sau đó, nhấp và kéo con trỏ chuột đến nơi bạn muốn CommandButton.
Bây giờ, bạn sẽ thấy CommandButton được chèn vào bảng tính.
Đầu tiên, Nhấp chuột phải vào CommandButton > chọn Thuộc tính gt; Sau đó, thay đổi Caption như bạn muốn. Đặt Name của nút là CmdSearch.
Sau đó, nhấp đúp vào nút CmdSearch.
Sau đó, viết đoạn mã sau:
Private Sub CmdSearch_Click()
Frm_Search.Show
End Sub
Phân tích mã:
Tại đây, một Thủ tục riêng tư đã được tạo bởi CommandButton. Thủ tục riêng tư chỉ áp dụng cho trang tính cụ thể mà nó được bật.
Sau đó, tôi đã sử dụng phương thức Show để hiển thị Frm_Search.
Sau đó, tôi kết thúc Thủ tục riêng tư.
Cuối cùng, lưu mã và quay lại trang tính của bạn.
Cách bạn có thể sử dụng hộp tìm kiếm mà bạn đã tạo trong UserForm.
Đầu tiên, nhấp vào CommandButton. Tại đây, UserForm sẽ xuất hiện.
Sau đó, chọn tùy chọn thả xuống để chọn Trường.
Sau đó, chọn Trường từ trình đơn thả xuống. Ở đây, tôi đã chọn “Địa chỉ”.
Bây giờ, tìm kiếm bất cứ điều gì bạn muốn. Ở đây, tôi đã tìm kiếm “v” và các Địa chỉ bắt đầu bằng “v” đã xuất hiện.
Tham khảo các chủ đề có liên quan khác: