Đây là 1 bài toán tình cờ phát hiện ra về phương thức Find.
Đây là code theo kiểu của bác BillHọc theo Help)
PHP:
Sub KiemTra2()
Dim HC As Long, i As Long, r As Long
Dim Mang As Range, TimThay As Range
Dim StrFind As String, CellDau As String
Sheets("DM.KH").Select
MaKhach.Chon.Default = True
MaKhach.Thoat.Cancel = True
MaKhach.LB.Clear
StrFind = Trim(MaKhach.TB.Text)
i = 0
Set Mang = Sheet3.Range("B5:C60000")
With Mang
Set TimThay = .Find(What:=StrFind)
If Not TimThay Is Nothing Then
CellDau = TimThay.Address
Do
r = TimThay.Row
MaKhach.LB.AddItem Cells(r, 2)
MaKhach.LB.List(i, 1) = Cells(r, 3)
i = i + 1
Set TimThay = .FindNext(TimThay)
Loop While Not TimThay Is Nothing And CellDau <> TimThay.Address
End If
End With
End Sub
Còn đây là code của bác Phạm Duy Long
PHP:
Sub KiemTra1()
Dim rng As String, rng1 As String, rng2 As String, StrFind As String
Dim r As Integer, i As Integer
Sheets("DM.KH").Select
MaKhach.Chon.Default = True
MaKhach.Thoat.Cancel = True
MaKhach.LB.Clear
StrFind = LCase(Trim(MaKhach.TB.Text))
rng1 = "$B$5"
rng = ""
On Error Resume Next
i = 0
Do
rng2 = Columns("B:C").Find(What:=StrFind, After:=Range(rng1)).Address
If Err.Number > 0 Or rng2 = rng Then Exit Do
rng1 = rng2
If rng = "" Then rng = rng2
If r <> Range(rng2).Row Then
r = Range(rng2).Row
MaKhach.LB.AddItem Cells(r, 2)
MaKhach.LB.List(i, 1) = Cells(r, 3)
i = i + 1
End If
Loop
End Sub
Một điều đáng ngạc nhiên là Code theo kiểu Bill bao giờ cũng mất gần gấp 2 thời gian.
Vậy các cao thủ cho hỏi : tại sao lại thế, nếu muốn làm như Bill và cải thiện về tốc độ thì như thế nào ???
Các bác xem File nhé .
To : ttphong : Sao cậu lại không cho vào thẻ PHP ??
Mình trước cũng có thử Find bằng kiemtra2, không chú ý đến tốc độ nhưng cách viết khó nhớ. Được cái là nó không gây lỗi khi không tìm thấy.
Còn kiemtra1 là mình record macro, chỉnh lại các biến theo yêu cầu.
Find record đầy đủ: Selection.Find(What:="aaa", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, atchCase:=False, SearchFormat:=False).Activate
Bỏ các mục chọn ngầm định, Find cò lại thế này: Selection.Find(What:=StrFind, After:=Range(rng1)).Activate Thấy nó gọn, dễ hiểu. Thay Activate bằng Address, Row, Column nó chạy ngọt thì dùng luôn.
As we have just covered the use of Excel's Lookup functions in our Excel section, it would be a good time to point out a common mistake made by some programmers. If you wish to perform a Lookup in VBA it seems that many opt to use the Vlookup WorksheetFunction in their code. While this can be done, it is a slow means of looking up data. This is because when we are in VBA we can use the extremely fast Find Method. However, before I show an example I fell it is extremely important to point out a couple of very often overlooked parts of this Method. See below from the Excel Help:
Remarks
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
If we do not remember this we are heading into possible problems, big time! I cannot tell you how many times I see the Find Method used incorrectly due to this. By far the best way to get the code needed for the Find Method is to use the Macro Recorder then modify the code.
Let's assume we have a table of data on Sheet1 within a range named Data and We wish to find the name Billy Brown obtain the value from 3 columns to the right on the correspoding row.
PHP:
Sub FindBillyBrown()
Dim vOurResult
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
If WorksheetFunction.CountIf(Sheet1.Range("Data"), "Billy Brown") > 0 Then
With Sheet1.Range("Data")
vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
End With
MsgBox vOurResult
End If
End Sub
Note that we have used the CountIf function to first find out whether the name even exits. The code does assume the named range Data exists on Sheet1 (CodeName). You will find this method, while it looks slower, is many times faster than VlookUp. It is also very flexible as we can offset to the right or left, depending on where we find the name. On the point of 'shorter code', it is a common misconception that the shorter the code the more efficient it must be. Nothing could be further from the truth! Often short code accounts for no errors and doesn't make use of Excel's built in features.
Let's now make this a bit more complicated by saying we might well have more than one occurrence of the name Billy Brown. The code as it is will only find the very first occurrence, which may not be what we want. Let's assume we want the name Billy Brown and it's corresponding result on the same row, 3 columns to the right BUT only if the value in the cell immediately to its left is 35 (the persons age).
PHP:
Sub FindBillyBrown35()
Dim vOurResult
Dim rFoundIt As Range
Dim iLoop As Integer
' FindBillyBrow Macro
' Using the Find Method over Vlookup
'
With Sheet1.Range("Data")
'Set variable to start search from
Set rFoundIt = .Cells(1, 1)
'Loop no more times that the name occurs
For iLoop = 1 To WorksheetFunction.CountIf _
(Sheet1.Range("Data"), "Billy Brown")
'ReSet variable to found occurence of name. Next loop search _
will start AFTER this Set cell.
Set rFoundIt = .Find(What:="Billy Brown", After:=rFoundIt, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'Check if it's the right one.
If rFoundIt.Offset(0, -1).Value = 35 Then
vOurResult = rFoundIt.Offset(0, 3)
Exit For 'Leave loop
End If
Next iLoop
End With 'Sheet1.Range("Data")
If Not IsEmpty(vOurResult) Then 'Variable holds a value
MsgBox vOurResult
End If
End Sub
Khi chúng ta muốn tìm kiếm một mục trong một range hay trong một worksheet, thông thường chúng ta sẽ dùng vòng lập For…Next loop. Điều này không có gì sai, cho tới khi vùng tìm kiếm của chúng ta nhỏ. Một khi vùng tìm kiếm lớn dần, lớn dần thì vòng lập For…Next sẽ không còn thích hợp nữa bởi vì việc tìm kiếm sẽ rất lâu. Chúng ta có một cách nào khác không?
Vâng, dĩ nhiên chúng ta có cách khác. Trong Excel chúng ta vào 'Edit' > 'Search' hoặc nhấn tổ hợp phím CTRL + F. Chúng ta có thể lập trình tương tự như việc tìm kiếm này. Tôi sẽ cố gắng thuyết phục bạn tại sao bạn phải dùng nó. Tạo sao tôi phải dùng phương thức Find?
Lý do chính tại sao bạn phải dùng nó chính là tốc độ tìm kiếm. Nếu bạn không muốn trong lúc chương trình tìm kiếm của mình thực hiện, thì mình phải ngủ gục thì bạn nên dùng phương thức Find.
Sau đây là môt ví dụ nhỏ để chứng mình điều tôi đã nói ở trên.
Đầu tiên tôi nhập "Yahoo, I'm here" vào ô IV65536 của Sheet1 và tôi thực thi đoạn code sau:
Mã:
Sub DaQuick()
If Not Sheet1.Cells.Find("Yahoo, I’m here") Is Nothing Then MsgBox "I found you"
End Sub
Bây giờ nếu bạn cần đi công việc thì hãy thực hiện đoạn code sau:
Mã:
Sub DaSloooooow()
Dim R As Range
For Each R In Sheet1.Cells
If R.Value = "Yahoo, I’m here" Then MsgBox "I found you"
Next R
End Sub
Bạn có tin chưa?
Như bạn đã được báo trước, thủ tục đầu tiên tìm kiếm rất nhanh trong khi đó thủ tục thứ hai tìm kiếm rất lâu (1 phút 57 giây trên máy có cấu hình 2.4Ghz!).
Được rồi, phương thức Find thì nhanh, nhưng nó làm việc như thế nào?
Đầu tiên chúng ta hãy xem đối số của phương thức này :
expression - Bắt buộc. Một biểu thức trả về đối tượng Range. What - Biến bắt buộc. Dữ liệu để tìm kiếm. Dữ liệu có thể là chuổi hay bất kỳ kiểu dữ liệu nào của Excel. After - Biến không bắt buộc. Ô mà bạn muốn bắt đầu tìm kiếm. Nó tương đương với ô hiện tại (active cell) khi bạn tìm kiếm trong Excel bằng việc nhấn tổ hợp phím Ctrl + F. Chú ý rằng After phải là một ô (single cell) trong một range. Nên nhớ rằng việc tìm kiếm bắt đầu từ sau ô này. Nếu bạn bỏ qua biến này, việc tìm kiếm sẽ bắt đầu sau ô ở góc trái-trên (upper-left) của range. LookIn - Biến không bắt buộc. Kiểu thông tin (The type of information). LookAt - Biến không bắt buộc. Có thể là một trong các hằng XlLookAt sau: xlWhole hoặc xlPart. SearchOrder - Biến không bắt buộc. Có thể là một trong các hằng XlSearchOrder sau: xlByRows hoặc xlByColumns. SearchDirection - Không bắt buộc XlSearchDirection. Hướng tìm kiếm, có thể là một trong các hằng XlSearchDirection sau: xlNext (mặc định) hoặc xlPrevious MatchCase - Biến không bắt buộc. True nếu bạn muốn tìm kiếm case sensitive. Mặc định là False. MatchByte - Biến không bắt buộc. Chỉ được dùng nếu bạn đã chọn hoặc đã cài đặt hổ trợ ngôn ngữ kiểu double-byte (double-byte language support). True to have double-byte characters match only double-byte characters. False to have double-byte characters match their single-byte equivalents
Ghi chú
Các thiết lập cho LookIn, LookAt, SearchOrder, MatchByte, và mục tìm kiếm được lưu mỗi lần bạn dùng phương thức này. Nếu bạn không chỉ định các giá trị cho các đối số này, các giá trị được lưu sẽ được dùng cho lần kế tiếp khi bạn gọi phương thức này. Việc thiết lập các đối số này thay đổi các thiết lập trong hộp tìm kiếm (Find dialog box), và thay đổi các thiết lập trong hộp tìm kiếm thay đổi các giá trị được lưu được dùng nếu bạn bỏ qua các đối số này. Để giải quyết vấn đề này, hãy thiết lập các đối số này một cách rõ ràng mỗi lần bạn dùng phương thức này.
À, điều này đã rõ rồi, nhưng tôi dùng nó như thế nào?
Có rất nhiều cách dùng phương thức Find, nhưng chúng ta hãy bắt đầu với cách cơ bản nhất:
Mã:
If Not Sheet1.Cells.Find("Yahoo, I’m here") Is Nothing Then MsgBox "I found you"
Trong đoạn code ở trên chúng ta kiểm tra chuổi có trong worksheet hay không. Nếu không tìm được, phương thức sẽ trả về nothing, ngược lại nếu tìm được sẽ hiện thông báo. Đoạn code này so sánh với
Mã:
Application.Worksheetfunction.CountIf.
Chúng ta chỉ kiểm tra xem chuổi có tồn tại không mà thôi.
Nếu chúng ta muốn phương thức Find trả về những thông tin hữu ích hơn thì chúng ta có thể khai báo và sử dụng kết quả như một (single cell) range.
Mã:
Dim R As Range
Set R = Range("A1:B1234").Find("DaDamnMethod")
'If a match is found then return the value of the cell at the right of R
If Not R Is Nothing Then MsgBox R.Offset(0, 1).Value
Set R = Nothing
Chúng ta có thể sử dụng range này như các range khác trong Excel. Format nó, xóa, thay đổi hay sửa nội dung của nó.
Không giống như các ví dụ ở trên ví dụ sau sẽ trả về một lỗi nếu không tìm thấy. Đây là một thói quen lập trình không tốt.
Mã:
Cells.Find("YouNameIt").Offset(3, 2).Select
Nếu tôi muốn tìm kiếm các tồn tại khác của mục tìm kiếm thì sao? (Tức là trong dữ liệu tìm kiếm có nhiều mục bạn cần tìm)
Nếu vậy bạn hãy dùng phương thức FindNext or FindPrevious và vòng lập Do…While loop. Bạn hãy xem ví dụ sau:
Mã:
Sub WhereIsIt()
Dim R As Range, FindAddress As String
'Set the range in which we want to search in
With Sheet1.Range("A1:N300")
'Search for the first occurrence of the item
Set R = .Find("Aha, here it is")
'If a match is found then
If Not R Is Nothing Then
'Store the address of the cell where the first match is found in a variable
FindAddress = R.Address
Do
'Color the cell where a match is found yellow
R.Interior.ColorIndex = 6
'Search for the next cell with a matching value
Set R = .FindNext(R)
'Search for all the other occurrences of the item i.e.
'Loop as long matches are found, and the address of the cell where a match is found,
'is different from the address of the cell where the first match is found (FindAddress)
Loop While Not R Is Nothing And R.Address <> FindAddress
End If
End With
'Clear memory
Set R = Nothing
End Sub
Khi việc tìm kiếm tới ô cuối cùng, nó sẽ trở lại ô đầu tiên. Để ngừng việc tìm kiếm lập lại, chúng ta lưu địa chỉ ô tìm thấy đầu tiên và kiểm tra địa chỉ mỗi khi một ô được tìm thấy.
Nhưng trong trường hợp chúng ta muốn sửa chữa hoặc xóa các mục được tìm thấy, đoạn code ở trên sẽ trả về lỗi mỗi khi chúng ta thay đổi hoặc xóa các mục được tìm thấy. Trong trường hợp này, chúng ta nên thay đoạn code (nếu không việc tìm kiếm sẽ không thể trở về ô được tìm thấy đầu tiên):
Mã:
Loop While Not R Is Nothing And R.Address <> FindAddress
Bằng đoạn code
Mã:
'If no more match is found, then exit sub
If R Is Nothing Then Exit Do
'Loop as long the address of the cell where a match is found,
'is different from the one where the first match was found
Loop While R.Address <> FindAddress
Còn điều gì tôi cần phải biết về phương thức Find không?
Chúng ta có thể ngăn người dùng trong việc biết được bạn đã tìm kiếm gì bằng việc resetting hộp thoại tìm kiếm (the Find dialog box) về giá trị mặc định. Các bạn hãy thêm đoạn code sau:
Mã:
Dim R As Range
Set R = Range("A1:B1234").Find("Patatten en sausissen", LookIn:=xlFormulas, lookat:=xlPart)
'If a match is found then return the value of the cell at the right of R
If Not R Is Nothing Then MsgBox R.Offset(0, 1).Value
'Reset the Find dialog to it's default settings
Set R = Cells.Find("", LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
Set R = Nothing
Tôi thử dữ liệu của Mr Okebab thấy kết quả nó có cái gì không ổn vì trùng nhiều quá. Tôi thêm vào 1 lable để báo số record tìm được và chỉnh lại dữ liệu, trong đó có 3 nhóm không trùng để tiện kiểm tra so sánh. Chạy Kiemtra1 và Kiemtra2, kết quả như sau: Mã khách_______________________________Tên khách AAA 0001 > AAA 0003__________________Công ty Cổ phần Đức Lân AAA (3 record)
mã khách và tên khách có AAA
Từ khóa tìm AAA Kiemtra1: 3 record (đúng) Kiemtra2: 6 record (sai. dư 3 dòng vì trùng lặp)
Mã khách_______________________________Tên khách
BMVEGA 0001 > BMVEGA 1153___________Cty Cổ phần Bạch Minh (1153 record) MINHCHINH 0001 > MINHCHINH 1154______Cty Cổ phần Minh Chính (1154 record) MINHTHANH 0001 > MINHTHANH 1154_____Cty TNHH Công nghiệp & T.Mại Minh Thành (1154 record)
Từ khóa tìm minh Kiemtra1: 3464 record (đúng BMVEGA 1153 +MINHCHINH 1154 + MINHTHANH 1154 = 3464) Kiemtra2: 5769 record (sai, BMVEGA 1153 +MINHCHINH 1154*2 + MINHTHANH 1154*2 = 5769 dư 2305 dòng vì trùng lặp)
Qua 2 trường hợp sai của Kiemtra2, tôi phát hiện thế này:
Kiemtra2 dư vì bên cột Mã khách có từ cần tìm thì ghi 1 record đồng thời cũng dòng đó bên khách hàng có từ đó cũng ghi 1 record.
Về tốc độ:
Kiemtra1: 3469 record 2,77 giây
Kiemtra2: 5769 record 4,44 giây Xét về mặt tốc độ so với số dòng ghi thì cả 2 tương đương
Mã:
Sub KiemTra1()
Dim rDau As String, StrFind As String
Dim rFind As Long, r As Integer, i As Integer
MaKhach.Chon.Default = True
MaKhach.Thoat.Cancel = True
MaKhach.LabFind = 0
MaKhach.LB.Clear
Sheets("DM.KH").Select
rc = Cells(4, 2).End(xlDown).Row
Set Mang = Range(Cells(4, 2), Cells(rc, 3))
StrFind = LCase(Trim(MaKhach.TB.Text))
rDau = "$B$4"
On Error Resume Next
rFind = Mang.FIND(What:=StrFind, After:=Range(rDau)).Row
If rFind = 0 Then Exit Sub
i = 0
Do
rFind = Mang.FIND(What:=StrFind, After:=Range(rDau)).Row
If rFind = 0 Or rFind <= Range(rDau).Row Then Exit Do
rDau = Cells(rFind, 3).Address
MaKhach.LB.AddItem Cells(rFind, 2)
MaKhach.LB.List(i, 1) = Cells(rFind, 3)
i = i + 1
Loop
MaKhach.LabFind = i
End Sub
Mã:
Sub KiemTra2()
Dim HC As Long, i As Long, r As Long
Dim Mang As Range, TimThay As Range
Dim StrFind As String, CellDau As String
Sheets("DM.KH").Select
MaKhach.Chon.Default = True
MaKhach.Thoat.Cancel = True
MaKhach.LB.Clear
StrFind = Trim(MaKhach.TB.Text)
i = 0
Set Mang = Sheet3.Range("B5:C60000")
With Mang
Set TimThay = .FIND(What:=StrFind)
If Not TimThay Is Nothing Then
CellDau = TimThay.Address
Do
r = TimThay.Row
MaKhach.LB.AddItem Cells(r, 2)
MaKhach.LB.List(i, 1) = Cells(r, 3)
i = i + 1
Set TimThay = .FindNext(TimThay)
Loop While Not TimThay Is Nothing And CellDau <> TimThay.Address
End If
End With
MaKhach.LabFind = i
End Sub
Kiemtra2 bị trùng vì nó xét lần lượt từng ô trong vùng chọn (biến Mang), thấy ô bên cột B hoặc cột C đúng tính 1 record.
Kiemtra1 kiểm soát được vấn đề này, nếu tìm thấy bên cột B hay C thì nhảy xuống tìm dòng bên dưới.
Học theo Help)
Còn đây là code của bác Phạm Duy Long
