Tổng hợp về phương thức tìm kiếm FIND (Find Method)

[hoangdanh282vn]

Find Method​

Phương thức Find tìm kiếm thông tin trong một vùng nào đó, kết quả trả về là ô đầu tiên chứa đựng thông tin được tìm thấy. Nếu không tìm thấy thông tin trong vùng tìm kiếm thì phương thức Find sẽ trả về Nothing.

Cú pháp phương thức như sau :

Expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Expression : Vùng tìm kiếm. Biểu thức Expression phải được khai báo và nó trả về một đối tượng Range.

What : Dữ liệu hay thông tin cần tìm kiếm. Đây là một đòi hỏi bắt buộc phải khai báo. Dữ liệu tìm kiếm có thể là một chuỗi ký tự hay một dạng dữ liệu nào đó có trong Excel và được khai báo dưới dạng Variant.

After : Ô được chọn để xác định vị trí tìm kiếm, là tùy chọn, được khai báo dưới dạng Variant, có thể bỏ qua. Việc tìm kiếm sẽ bắt đầu từ ô này. Ô này tương ứng với vị trí của ô hiện hành sau khi việc tìm kiếm hoàn tất. Ô xác định vị trí tìm kiếm này sẽ không được đưa vào quá trình tìm kiếm trừ khi vùng tìm kiếm bao gồm cả ô này. Nếu đối số này không được khai báo thì việc tìm kiếm sẽ bắt đầu sau ô trên cùng bên trái của vùng cần tìm kiếm.

LookIn : Không bắt buộc, khai báo dạng Variant. Đây là một dạng thông tin và thường có giá trị là xlValues.

LookAt : Cách thức tìm kiếm. Là đối số tùy chọn, không bắt buộc, khai báo dưới dạng Variant. LookAt có 2 giá trị : xlWhole (Tìm toàn bộ) hoặc xlPart (tìm một phần).

SearchOrder : Xác định dạng thứ tự tìm kiếm, là tùy chọn không bắt buộc, khai báo dưới dạng Variant. SearchOrder có 2 dạng ứng với 2 hàng số : xlByRows (theo thứ tự dòng) hoặc xlByColumns (theo thứ tự cột)

SearchDirection : Hướng tìm kiếm, là tùy chọn. SearchDirection có 2 dạng ứng với 2 hằng số : xlNext (Tìm kế tiếp, là giá trị mặc định), xlPrevious (Tìm trước đó)

MatchCase : Là tùy chọn để xác định kiểu tìm kiếm có phân biệt chữ Hoa với chữ thường, khai báo dưới dạng Variant. Khai báo là True nếu ta muốn tìm kiếm chính xác. Giá trị mặc định là False.( Không phân biệt chữ in Hoa với chữ thường)

MatchByte : Không bắt buộc, khai báo dạng Variant. Chỉ sử dụng khi ta đã chọn hoặc cài đặt bộ hỗ trợ ngôn ngữ ký tự byte kép. Là True nếu ứng với bộ ký tự byte kép, False nếu ứng với bộ ký tự byte đơn.

SearchFormat : Tìm kiếm theo định dạng. Là tham số tùy chọn, khai báo dưới dạng Variant.

Lưu ý :

- Các thiết lập cho các đối số LookIn, LookAt, SearchOrder và MatchByte sẽ được lưu mỗi lần ta sử dụng phương thức này (phương thức Find). Nếu ta không khai báo giá trị cho các đối số vào lần sử dụng phương thức Find tiếp theo, các giá trị thiết lập đã lưu trước đó sẽ được sử dụng. Việc thiết lập các đối số này làm thay đổi các tùy chọn thiết lập trong hộp thoại Find, và việc thay đổi các thiết lập trong hộp thoại Find sẽ làm thay đổi các giá trị đã lưu – là những giá trị được sử dụng nếu ta bỏ qua các đối số này. Để tránh xảy ra việc này, ta nên khai báo các đối số một cách rõ ràng mỗi lần sử dụng phương thức Find này.

- Ta có thể dùng phương thức FindNext và FindPrevious để lặp lại việc tìm kiếm. Khi đến vị trí cuối của vùng tìm kiếm được xác định trước đó, excel sẽ bao phủ từ vị trí này đến vị trí đầu tiên của vùng tìm kiếm. Để ngưng việc tìm kiếm ngay khi động tác bao phủ này xảy ra, hãy lưu lại địa chỉ của ô đầu tiên tìm được, sau đó thử so sánh lần lượt mỗi địa chỉ ô được tìm thấy kế tiếp với địa chỉ ô vừa được lưu này.

============================@@@===========================​

Bài viết này tham khảo dựa trên bài dịch nguyên bản của anh Ca_dafi
(Còn tiếp)

 

[hoangdanh282vn]

Các ví dụ minh họa về Find Method


​

Áp dụng : Để hiểu rõ hơn về phương thức tìm kiếm Find, ta xét một vài ví dụ minh họa sau :

1.> Ta có dữ liệu như sau :

​

Ta sẽ dùng phương thức Find để tìm ra trong vùng A1:A10 những ô nào có ký tự 1
Ta dùng chuột bôi đen vùng A1:A10 và cho chạy Macro sau :

Sub Test1()
    Dim Rng As Range
    Set Rng = Selection.Find("1*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not Rng Is Nothing Then
        Do
            MsgBox Rng.Address
            Set Rng = Selection.FindNext(Rng)
        Loop
    End If
End Sub

Lần lượt các kết quả của việc chạy Macro như sau :

Diễn giải về trình tự hoạt động của Macro như sau :

- Tìm trong vùng từ ô A2 đến ô A10 xem ô nào có chứa ký tự 1. Kết quả là ô A3 được tìm thấy đầu tiên và sẽ gán biến Rng bằng ô A3. Các bạn sẽ hỏi là tại sao không tìm bắt đầu từ ô A1 và tại sao không trả về ô A1 đầu tiên mà lại là ô A3. Các bạn xem trong cấu trúc của phương thức Find thì sẽ thấy có một đối số tùy chọn, đó là After. Đây là tham số dùng để xác định vị trí bắt đầu của việc tìm kiếm, việc tìm kiếm sẽ bắt đầu tại vị trí sau ô này. Nếu không khai báo thì việc tìm kiếm sẽ bắt đầu ngay sau ô đầu tiên bên trái của vùng tìm kiếm. Như VD trên thì vùng tìm kiếm Selection (A1:A10), do không khai báo nên việc tìm kiếm sẽ bắt đầu từ ô A2 và ô A3 được tìm thấy đầu tiên.

- Tham số What trong phương thức Find ở VD trên sử dụng thêm ký tự “*” để tìm gần đúng (chuỗi bắt đầu bởi số 1). Bỏ dầu “*” ta sẽ tìm chính xác.

- Nếu như không tìm thấy ô nào trong vùng Selection có chứa số 1 (Biến Rng = Nothing) thì sẽ thoát khỏi thủ tục. Ngược lại, sẽ bắt đầu vào vòng lặp tìm kiếm Do Loop.

- Sau khi tìm thấy và gán biến Rng cho ô A3 thì hiện hộp thoại thông báo về địa chỉ của ô vừa được tìm thấy ( biến Rng). Khi đó tham số After sẽ là ô A3 và việc tìm kiếm sẽ bắt đầu sau ô này.

- Tiếp theo ta sẽ dùng phương thức FindNext(Rng) để tìm đến ô chứa số 1 tiếp theo và ô A5 được tìm thấy, biến Rng sẽ là ô A5 . Quá trình cứ tiếp tục như thế cho đến ô A10.

- Khi dò tìm đến ô A10 thì quá trình dò tìm sẽ được lặp lại như lúc ban đầu, tức là sẽ tìm kiếm lặp lại từ ô A1 đến ô A10. Nhưng khi đó tham số After sẽ là ô A10 và lúc này ô A1 sẽ được tìm thấy. Và tiếp đến là ô A3, A5…sẽ tìm kiếm mãi mãi.

- Khi gán biến Rng bằng ô A5 thì đến từ khóa Loop. Khi gặp từ khóa này thì quá trình tiềm kiếm sẽ được tiếp tục, đến từ khóa Do, hiện hộp thông báo và lại gán biến Rng bằng ô A6….



Đoạn Code trên còn 2 vấn đề cần khắc phục, đó là làm sao để tìm thấy được ô A1 đầu tiên và sẽ kết thúc quá trình tìm kiếm khi dò tìm đến ô A10.

Đoạn Code sau sẽ khắc phục điều này :

Sub Test2()
    Dim Rng As Range, LastCell As Range, FirstAddress As String
    Set LastCell = Selection.Cells(Selection.Cells.Count)
    Set Rng = Selection.Find("1*", After:=LastCell, LookIn:=xlValues, LookAt:=xlWhole)
    FirstAddress = Rng.Address
    If Not Rng Is Nothing Then
        Do
            MsgBox Rng.Address
            Set Rng = Selection.FindNext(Rng)
        Loop While FirstAddress <> Rng.Address
    End If
End Sub

- Đầu tiên ta sẽ gán biến LastCell ứng với ô cuối cùng của vùng cần tìm kiếm : LastCell = [A10]

- Trong phương thức Find ta sẽ thấy có xuất hiện thêm tham số After có giá trị là LastCell. Điều này có nghĩa là việc tìm kiếm sẽ bắt đầu sau ô [A10]. Và như thế là ô [A1] sẽ được tìm thấy đầu tiên. Sau đó gán biến Rng bằng ô [A1].

- FirstAddress là chuỗi text ứng với địa chỉ của ô được tìm thấy đầu tiên trong vùng, ta gán biến FirstAddress = ”$A$1”.

- Nếu Rng khác Nothing thì đi đến vòng lặp Do Loop, nếu là Nothing thì thoát khỏi thủ tục.

- Vào vòng lặp Do loop thì quá trình tương tự như đoạn Sub Test1. Tuy nhiên trong đoạn Code này còn có thêm một biểu thức điều kiện : FirstAddress <> Rng.Address.

- Loop While FirstAddress <> Rng.Address : đoạn này có ý nghĩa như sau : quá trình tìm kiếm sẽ được lặp lại nếu như địa chỉ của biến Rng hiện tại khác với địa chỉ của ô đầu tiên được tìm thấy trong vùng vùng ( ô A1). Ngược lại, nếu trùng thì có nghĩa rằng ô này đã được tìm thấy trước đó, sẽ hủy bỏ kết quả này và thoát khỏi thủ tục.

 

[hoangdanh282vn]

Qua 2 ví dụ trên ta đã hiểu được phần nào về phương thức Find.

Áp dụng vào thực tế thì ta xem tiếp các bài tập sau :

Cũng với dữ liệu này :

Bài tập 1.> Yêu cầu đặt ra là tìm trong vùng A1:A10 những ô nào có chứa số 1 thì copy và dán lần lượt vào cột B. Bài toán này tương tự như việc trích lọc ra danh sách theo điều kiện trích lọc.

Ta xem đoạn code sau :

Sub TrichLoc1()
    Dim Rng As Range, LastCell As Range, FirstAddress As String
    Set LastCell = Selection.Cells(Selection.Cells.Count)
    Set Rng = Selection.Find("1*", After:=LastCell, LookIn:=xlValues, LookAt:=xlWhole)
    FirstAddress = Rng.Address
    If Not Rng Is Nothing Then
        R = 1
        Do
            Cells(R, 2) = Rng
            R = R + 1
            Set Rng = Selection.FindNext(Rng)
        Loop While FirstAddress <> Rng.Address
    End If
End Sub

Đoạn Code này cũng tương tự như đoạn Code trong thủ tục Test2. Chỉ khác ở chỗ là thay vì sau mỗi lần tìm thấy ô thỏa mãn điều kiện sẽ hiện bảng thông báo địa chỉ của ô đó thì bây giờ sẽ điền giá trị của ô đó vào cột B.
- Nếu biến Rng khác Nothing thì gán cho biến R=1, vào vòng lặp.
- Điền vào ô Cells(1,2) = [B1] giá trị của biến Rng = 1.
- Tăng biến R lên 1 đơn vị R = 1+1 = 2
- Gán biến Rng cho ô tìm được tiếp theo : Rng = [A3]
- Kiểm tra điều kiện lặp, nếu thỏa thì qua trở lại Do
- Điền vào ô Cells(2,2) = [B2] giá trị của biến Rng = 11.
- Tiếp tục cho đến hết và kết quả như sau :

Bài tập 2>. Yêu cầu thứ 2 là tìm trong vùng A1:A10, C1:C5 những ô nào có chứa số 1 thì copy và dán lần lượt vào cột B.

Nếu ta vẫn áp dụng thủ tục trên cho yêu cầu này thì sẽ không chính xác, vì ta sẽ không xác định được ô LastCell.

Ta sẽ tìm hiểu đoạn Code sau :

Sub TrichLoc2()
    Dim RngData As Range, Rng As Range, LastCell As Range, FirstAddress As String
    Set RngData = Union(Range("A1:A20"), Range("C1:C5"))
    Set LastCell = Range("C1:C5").Cells(Range("C1:C5").Cells.Count)
    Set Rng = RngData.Find("1*", After:=LastCell, LookIn:=xlValues, LookAt:=xlWhole)
    FirstAddress = Rng.Address
    If Not Rng Is Nothing Then
        R = 1
        Do
            Cells(R, 2) = Rng
            R = R + 1
            Set Rng = RngData.FindNext(Rng)
        Loop While FirstAddress <> Rng.Address
    End If
End Sub

- Ở đây, ta sử dụng phương thức Union để ghép các vùng cần trích lọc lại với nhau.
- Tìm ô cuối cùng trong vùng C1:C5 và gán cho biến LastCell : LastCell = [C5].
- Các bước tiếp theo thì tương tự như các Code đã nêu trên
- Tiếp tục cho đến hết và kết quả như sau :

 

[hoangdanh282vn]

Tiếp theo là giải quyết một nhiệm vụ thực tiển về trích lọc Sổ cái tài khoản từ Sổ Nhật ký chung.

Ta có Dữ liệu mẫu như sau :

Vùng A111 là dữ liệu của Sổ NKC
Vùng F1:I11 là kết quả trích lọc TK 111 từ sổ NCK ( Sổ cái TK 111)
Ta xét Macro sau :

Sub TrichLocSocai()
    Dim Rng As Range, LastCell As Range
    Dim FirstAddress As String, R As Long, SaveRow As Long
    Application.ScreenUpdating = False
    With Range("B2:C11")
        Set LastCell = .Cells(.Cells.Count)
        Set Rng = .Find("111*", After:=LastCell, LookIn:=xlValues, LookAt:=xlWhole)
        FirstAddress = Rng.Address
        If Not Rng Is Nothing Then
            R = 2
            Do
                SaveRow = Rng.Row
                Select Case Rng.Column
                Case 2
                    Cells(R, 6) = Rng.Offset(, -1)
                    Cells(R, 7) = Rng.Offset(, 1)
                    Cells(R, 8) = Rng.Offset(, 2)
                Case 3
                    Cells(R, 6) = Rng.Offset(, -2)
                    Cells(R, 7) = Rng.Offset(, -1)
                    Cells(R, 9) = Rng.Offset(, 1)
                End Select
                R = R + 1
                Set Rng = .FindNext(Rng)
            Loop While FirstAddress <> Rng.Address And SaveRow <> Rng.Row
        End If
    End With
    Set LastCell = Nothing: Set Rng = Nothing
    Application.ScreenUpdating = True
End Sub

So với cách trích lọc dùng For Next thông thường thì Code này vượt trội hơn về tốc độ. Phù hợp với những cơ sở dữ liệu lớn.
Ta có thể kết hợp với thuộc tính End để làm cho dữ liệu tạo sự linh động cho dữ liệu gốc.

Trên đây là nội dung sơ lược về phương thức Find. Chúng ta sẽ cùng tìm hiểu sâu hơn về phương thức này trong các bài tiếp theo.

 

[ThuNghi]

Tạo sổ quỹ bằng Find Method!

Một cách để làm Sổ Quỹ từ NKC =>In phiếu Thu & Chi.​

(file này khá hoàn thiện chỉ có code là chưa hoàn thiện thôi.)

Các bạn làm kế toán có thể thực hiện.

Sub TaoSoQuy()
Dim TkDu As String, GhiChu As String
Dim RngFound As Range, LastCell As Range, MyRng As Range, SoTK As String
Dim eRow As Long, SoLan As Long, iR As Long, iL As Long, TienThu As Double, TienChi As Double
With Application
        .EnableEvents = False:          .DisplayAlerts = False
       .ScreenUpdating = False:       .Calculation = xlCalculationManual
End With
iR = 2000:                                   Sheet5.Select 'Sh SoQuy'
Range(Cells(9, 1), Cells(iR , 8)).ClearContents
Range(Cells(9, 10), Cells(iR , 10)).ClearContents
SoTK = "1111*"
iR = 8
With Sheets("NKC")
    eRow = .[I65000].End(xlUp).Row
    Set MyRng = .Range("I9:J" & eRow) 'Có sửa I10=I9'
    SoLan = WorksheetFunction.CountIf(MyRng, SoTK)
    Set RngFound = .Cells(9, 10) '10=9'
    For iL = 1 To SoLan
        With MyRng
           Set RngFound = .Find(SoTK, After:=RngFound, LookIn:=xlValues, LookAt:=xlWhole)
                With RngFound
                    Select Case RngFound.Column
                    Case 9
                            TienThu = TienThu + .Offset(, 2).Value
                            If InStr(GhiChu, .Offset(, -5)) > 0 Then
                                GhiChu = Trim(GhiChu)
                            Else
                                GhiChu = GhiChu & .Offset(, -5) & ";"
                            End If
                            If InStr(TkDu, .Offset(, 1)) > 0 Then
                                TkDu = TkDu
                            Else
                                TkDu = TkDu & .Offset(, 1) & ";"
                            End If
                            If .Offset(, -6) <> .Offset(1, -6) Then
                                 iR = iR + 1
                                 Cells(iR, 1) = iR - 8
                                 Cells(iR, 3) = .Offset(, -6) 'SoCT'
                                 Cells(iR, 2) = .Offset(, -4) 'NgayHT'
                                 Cells(iR, 5) = .Offset(, -1) & "-" & .Offset(, -2)'DienGiai'
                                 Cells(iR, 6) = TkDu 'TkDu'
                                 Cells(iR, 7) = TienThu 'TienThu'
                                 Cells(iR, 10) = GhiChu 'Note'
                                 TienThu = 0: TkDu = "": GhiChu = ""
                              End If
                        Case 10
                            TienChi = TienChi + .Offset(, 1).Value
                                If InStr(GhiChu, .Offset(, -6)) > 0 Then
                                    GhiChu = Trim(GhiChu)
                                Else
                                    GhiChu = GhiChu & .Offset(, -6) & ";"
                                End If
                            If InStr(TkDu, .Offset(, -1)) > 0 Then
                                TkDu = TkDu
                            Else
                                TkDu = TkDu & .Offset(, -1) & ";"
                            End If
                            If .Offset(, -7) <> .Offset(1, -7) Then '*Neu so ct khac so ct'
                                iR = iR + 1
                                Cells(iR, 1) = iR - 8
                                Cells(iR, 4) = .Offset(, -7) 'SoCT'
                                Cells(iR, 2) = .Offset(, -5) 'NgayHT'
                                Cells(iR, 5) = .Offset(, -2) & "-" & .Offset(, -3)'DienGiai'
                                Cells(iR, 6) = TkDu 'TkDu'
                                Cells(iR, 8) = TienChi 'Tienchi'
                                Cells(iR, 10) = GhiChu 'Note'
                                TienChi = 0: TkDu = "": GhiChu = ""
                            End If
                        End Select
                End With
            End With
        Next iL
End With
Set LastCell = Nothing: Set RngFound = Nothing: Set MyRng = Nothing
With Application
        .EnableEvents = True:          .DisplayAlerts = True
       .ScreenUpdating = True:       .Calculation = xlCalculationAutomatic
End With
End Sub

Trong đây có phần tính tồn cuối ngày làm biếng nên chỉ dùng công thức.
Danh có thể diễn giải code hộ với. Làm rồi mà ngán coi lại.
Làm rồi nên up, vì vàì bạn có nhu cầu.
(Chú ý là khi nhập vào NKC phải theo cấu trúc.)

 

[Thien]

To ThuNghi

Hay đấy. Mới học vòng lặp lấy dữ liệu từ OB (Mr Hiếu) chưa xong nay thấy cái này hay quá (về tốc độ).

Bạn cho 1 ví dụ hoàn chỉnh 1 chút nhen: từ NKC lấy dữ liệu lên sổ Cái các TK, Sổ Quỹ, Phiếu nhập xuất kho, phiếu thu chi, Thẻ kho (Sổ chi tiết vật tư).

Từ đây từ từ học được rùi.

Bạn giúp nhen.

Thân.


P/s: File FindGPE06-TaoSoQuy.rar của bạn chưa hoàn chỉnh đâu nhé:
+ Chưa ẩn dòng thừa.
+ Chưa tính tồn cuối kỳ.

Đề nghị các bạn & tác giả bài này đến đây để thảo luận các vấn đề có liên quan:

http://giaiphapexcel.com/forum/showthread.php?t=15175​

(Bài này sẽ xóa trong thời gian tới!)

 

[ThuNghi]

To ThuNghi

Hay đấy. Mới học vòng lặp lấy dữ liệu từ OB (Mr Hiếu) chưa xong nay thấy cái này hay quá (về tốc độ).

Bạn cho 1 ví dụ hoàn chỉnh 1 chút nhen: từ NKC lấy dữ liệu lên sổ Cái các TK, Sổ Quỹ, Phiếu nhập xuất kho, phiếu thu chi, Thẻ kho (Sổ chi tiết vật tư).
P/s: File FindGPE06-TaoSoQuy.rar của bạn chưa hoàn chỉnh đâu nhé:
+ Chưa ẩn dòng thừa.
+ Chưa tính tồn cuối kỳ.

Đề nghị các bạn & tác giả bài này đến đây để thảo luận các vấn đề có liên quan:

http://giaiphapexcel.com/forum/showthread.php?t=15175​

(Bài này sẽ xóa trong thời gian tới!)

Cám ơn nhiều, ít ra cũng có 1 người cần, trong file trên là tôi lấy dữ liệu thực tế mà làm đó. Còn vấn đề bỏ dòng, tổng thì lúc nào OK thì hòan thiện luôn. File FindGPE04 là file in phiếu nhập, dồn 2 file lại và code của Danh nữa là tạo được tòan bộ sổ kế toán theo từng tháng.
Bác HYEN17 ơi, giờ em muốn sửa file, hòan thiện thì vào trang kia hả. Bác chuyển hết toàn bộ file của em qua topic đó với. Em tính up thêm FindGPE07 mà chưa biết up ở đâu.
Thông cảm cho vì em muốn một lấn làm là làm cụ thể và em ứng dụng luôn nên có thể không tập trung về Find nhưng Find vẫn là Method xuyên suốt.
Cám ơn Bác!

 

[Lệnh Hồ Đại Hiệp]

Một cách để làm Sổ Quỹ từ NKC =>In phiếu Thu & Chi.​

Set RngFound = .Find(SoTK, After:=RngFound, LookIn:=xlValues, LookAt:=xlWhole)

1. Bản chất của Find là sẽ lưu lại các giá trị tham số của lần tìm kiếm trước đó. Vì vậy nếu lần trước đó được Set là :

SearchOrder:=xlByColumns

Thì như vậy theo như Code trên sẽ tìm kiếm theo cột, hết cột NỢ mới đến cột CÓ

Điều này cũng có nghĩa rằng nếu lần tìm kiếm trước đó là theo cột thì Code trên sẽ tìm hết Phiếu THU sau đó mới đến Phiếu CHI, vì vậy nếu để cho đúng Sổ Quỹ thì khi chạy xong ta lại phải Sort lại một lần nữa (Code trên không có điều này)

Giải pháp là : Ta thêm vào thành :

Set RngFound = .Find(SoTK, After:=RngFound, LookIn:=xlValues, LookAt:=xlWhole, SearchOder:=xlRows)

Như vậy nó sẽ tìm kiếm theo hàng. Đây là một lợi thế khi DATA của ta đã được Sort theo ngày tháng

2. Sổ quỹ thì làm theo tháng hoặc năm, thậm chí là ngày bất kỳ, tuy nhiên Code trên lại không thấy việc so sánh hay loại trừ nếu ngày chứng từ không phù hợp.
Vì DATA là trọn 1 tháng, Sổ Quỹ cũng trong 1 tháng nên ta mới thấy nó luôn đúng

3. Chứng từ thu chi thường có ký hiệu là PTxxx, PCxxx, ta có thể lợi dụng điều này để xác định vùng tìm kiếm chỉ là ở cột chứng từ. Có thể có 2 cách:
- Tìm Phiếu Thu trước, Phiếu Chi sau, sau đó Sort lại theo ngày tháng
- Tìm đồng thời cả 2 phiếu với 2 phương thức Find chạy song song nhau

4. Việc sử dụng biến trung gian là rất tốt, tuy nhiên tôi vẫn thấy bác dùng nhiều biến quá. Có thể rút gọn hơn rất nhiều. Khi chạy một khối lượng lớn thì bác mới thấy việc này hiệu quả.

Chúc vui!

 

[ThuNghi]

1/Set RngFound = .Find(SoTK, After:=RngFound, LookIn:=xlValues, LookAt:=xlWhole, SearchOder:=xlRows)

Nếu không ghi SearchOder:=... thì mặc định là SearchOder:=xlRows

2. Sổ quỹ thì làm theo tháng hoặc năm, thậm chí là ngày bất kỳ, tuy nhiên Code trên lại không thấy việc so sánh hay loại trừ nếu ngày chứng từ không phù hợp.
Vì DATA là trọn 1 tháng, Sổ Quỹ cũng trong 1 tháng nên ta mới thấy nó luôn đúng

Thêm cái này vào thì OK thôi, trong NKC có Field Thang, có thể tìm theo field này. Đơn giản đã.

3. Chứng từ thu chi thường có ký hiệu là PTxxx, PCxxx, ta có thể lợi dụng điều này để xác định vùng tìm kiếm chỉ là ở cột chứng từ. Có thể có 2 cách:
- Tìm Phiếu Thu trước, Phiếu Chi sau, sau đó Sort lại theo ngày tháng
- Tìm đồng thời cả 2 phiếu với 2 phương thức Find chạy song song nhau

Cũng tính Tìm Phiếu Thu trước, Phiếu Chi sau, và 2 phương thức Find chạy song song, đã thử thấy rắc rối quá. Mà chưa chắc nhanh hơn.

4. Việc sử dụng biến trung gian là rất tốt, tuy nhiên tôi vẫn thấy bác dùng nhiều biến quá. Có thể rút gọn hơn rất nhiều. Khi chạy một khối lượng lớn thì bác mới thấy việc này hiệu quả.

Phần case này có thể rút gọn lại vì có sự đồng nhất là +/-1, chưa hoàn thiện. Đang làm lại.
Cám ơn đóng góp của bạn rất nhiều. Trước giờ mình quen dùng Advace Filter, sang Find cũng còn phải học hỏi nhiều.

 

[hoangdanh282vn]

Ở các bài viết trên chúng ta đã tìm hiểu về phương thức Find kết hợp với FindNext. Hôm nay ta sẽ nghiên cứu về phương thức FindPrevious.

Ta xét VD sau : Cho dữ liệu :

Tìm trong vùng A1:A10 từ dưới lên trên, những ô nào có chứa ký tự “a” (Có phân biệt chữ in hoa với chữ thường) thì copy và dán lần lượt vào cột B. Kết quả mong muốn như hình minh họa ở trên.


Ta bôi đen vùng A1:A10 và chạy đoạn Code sau :

Sub Test3()
    Dim Rng As Range, FirstCell As Range, FirstAddress As String, r As Long
    Application.ScreenUpdating = False
    On Error Resume Next
    With Selection
        Set FirstCell = .Resize(1, 1)
        Set Rng = .Find(What:="*a*", After:=FirstCell, LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchDirection:=xlPrevious, MatchCase:=True)
        FirstAddress = Rng.Address
        If Not Rng Is Nothing Then
            r = 1
            Do
                Cells(r, 2) = Rng
                r = r + 1
                Set Rng = .FindPrevious(Rng)
            Loop While FirstAddress <> Rng.Address
        End If
    End With
    Set FirstCell = Nothing: Set Rng = Nothing
    Application.ScreenUpdating = True
End Sub

</SPAN>
Diễn giải trình tự nội dung của Code như sau :

- Dim Rng as Range, … : khai báo biến trong thủ tục.
- Application.ScreenUpdating = False : Tạm dừng chức năng cập nhật màn hình của ứng dụng.
- On Error Resume Next : câu lệnh điều khiển chương trình. Nếu trong quá trình thi hành thủ tục mà gặp lỗi thì bỏ qua lỗi này và thi hành tiếp các câu lệch tiếp theo.
- Bắt đầu sử dụng câu lệnh With cho đối tượng Selection.
- Gán biến FirstCell bằng với ô đầu tiên trong vùng Selection : FirstCell = [A1]
- Gán biến Rng cho kết quả tìm kiếm đầu tiên của phuơng thức Find.
+ Giá trị tìm kiếm What:=”*a*” có nghĩa là tìm trong vùng Selection tất cả các ô có chứa đựng ký tự “a”.
+ After:=FirstCell : Bắt đầu tìm sau ô FirstCell.
+ SearchDirection:=xlPrevious : có nghĩa là sẽ tìm ngược từ dưới lên trên trong vùng dữ liệu. Ở các bài trước ta không thấy tham số SearchDirection xuất hiện trong phương thức Find, điều này có nghĩa là tham số mặc định sẽ là SearchDirection:=xlNext : Tìm xuôi từ trên xuống dưới.
+ MatchCase:=True : Tìm kiếm có phân biệt chữ thường với chữ in hoa.
Như vậy, biến Rng ngay tại thời điểm này sẽ là Rng = [A8].
- Gán biến FirstAddress bằng địa chỉ của ô vừa được tìm thấy đầu tiên : FirstAddress = ” $A$8”
- Nếu biến Rng khác Nothing thì :
- Gán cho biến r = 1
- Vào vòng lặp Do Loop
- Cells(r, 2) = Rng : điền giá trị của biến Rng vào ô Cells(1,2). Vậy ô [B1] = “Bac”.
- Tăng biến r thêm 1 đơn vị : r = r + 1 = 2
- Gán biến Rng cho ô vừa được tìm thấy kế tiếp (tìm từ dưới lên trên bắt đầu từ ô [A8]). Rng = [A6].
- Nếu địa chỉ của ô vừa tìm thấy khác với địa chỉ của ô đầu tiên được tìm thấy thì tiếp tục vòng lặp Do Loop. Ngược lại thì thoát khỏi vòng lặp.
- End If, End With : kết thúc mệnh đề If và With.
- Gán cho biến FirstCell và biến Rng bằng Nothing : nhằm làm giảm sự chiếm dụng bộ nhớ, giúp thủ tục chạy nhanh hơn.
- Application.ScreenUpdating = True :Khôi phục lại chức năng cập nhật màn hình của ứng dụng.
- Kết thúc thủ tục.

 

[ThuNghi]

Ở các bài viết trên chúng ta đã tìm hiểu về phương thức Find kết hợp với FindNext. Hôm nay ta sẽ nghiên cứu về phương thức FindPrevious.

Ta xét VD sau :
Cho dữ liệu :
Tìm trong vùng A1:A10 từ dưới lên trên, những ô nào có chứa ký tự “a” (Có phân biệt chữ in hoa với chữ thường) thì copy và dán lần lượt vào cột B. Kết quả mong muốn như hình minh họa ở trên.

Cái này áp dụng thay thế hàm Match tìm từ dưới lên. Nếu OK thì lấy và exit.

 

[Lệnh Hồ Đại Hiệp]

Nếu không ghi SearchOder:=... thì mặc định là SearchOder:=xlRows

Bác hãy thử với chính Code của trên của bác thì thấy ngay thôi:

Thử 1:

1. Chạy Code với SearchOrder:=xlRows để lấy giá trị mặc định
2. Chạy Code không có SearchOrder --->> Nó sẽ lấy giá trị mặc định là xlRows

Thử 2 :

1. Chạy Code với SearchOrder:=xlColumns để lấy giá trị mặc định
2. Chạy Code không có SearchOrder --->> Nó sẽ lấy giá trị mặc định là xlColumns

Khi đó sẽ cho ra 2 Sổ Quỹ khác nhau.

Chúc vui

 

[ThuNghi]

Thử 1:

1. Chạy Code với SearchOrder:=xlRows để lấy giá trị mặc định
2. Chạy Code không có SearchOrder --->> Nó sẽ lấy giá trị mặc định là xlRows

Thử 2 :

1. Chạy Code với SearchOrder:=xlColumns để lấy giá trị mặc định
2. Chạy Code không có SearchOrder --->> Nó sẽ lấy giá trị mặc định là xlColumns

Khi đó sẽ cho ra 2 Sổ Quỹ khác nhau.

Chúc vui

Rất cám ơn, nếu không nhắc chắc không nghĩ ra.

 

[Lệnh Hồ Đại Hiệp]

Ở các bài viết trên chúng ta đã tìm hiểu về phương thức Find kết hợp với FindNext. Hôm nay ta sẽ nghiên cứu về phương thức FindPrevious.

Ta xét VD sau :
Cho dữ liệu :

​

Tìm trong vùng A1:A10 từ dưới lên trên, những ô nào có chứa ký tự “a” (Có phân biệt chữ in hoa với chữ thường) thì copy và dán lần lượt vào cột B. Kết quả mong muốn như hình minh họa ở trên.

Ta xét đoạn Code sau :

Sub Test3()
Dim Rng As Range, FirstCell As Range, FirstAddress As String, r As Long
Application.ScreenUpdating = False
On Error Resume Next
With Selection
Set FirstCell = .Resize(1, 1)
Set Rng = .Find(What:="*a*", After:=FirstCell, LookIn:=xlValues, LookAt:=xlWhole, _
                    SearchDirection:=xlPrevious, MatchCase:=True)

</SPAN>

​

Để :

Tìm trong vùng A1:A10 từ dưới lên trên, những ô nào có chứa ký tự “a” (Có phân biệt chữ in hoa với chữ thường)

thì có thể làm như sau nữa :

Set Rng = .Find(What:="a", After:=FirstCell, LookIn:=xlValues, LookAt:=xlPart, _
                    SearchDirection:=xlPrevious, MatchCase:=True)

 

[ThuNghi]

Tạo sổ quỹ, sổ cái bằng Find Method!

Bổ sung lại file FindGPE06, tạo thêm phần trích sổ cái, còn phiếu thu chi nếu tạo sổ quỹ xong thì in phiếu thu chi chỉ là vlookup. Bạn nào hòan thiện hộ.
Các bác xem và góp ý cho code tạo sổ quỹ giúp.
Xin cám ơn!

Sub TaoSoQuy()
With Application
        .EnableEvents = False:        .DisplayAlerts = False:        .ScreenUpdating = False:       .Calculation = xlCalculationManual
End With
Dim TkDu As String, GhiChu As String, SoTK As String
Dim RngFound As Range, LastCell As Range, MyRng As Range
Dim eRow As Long, SoLan As Long, iR As Long, iL As Long, eR As Long, iC As Long
Dim SoTien As Double, TienTon As Double

iR = 8: eR = 1000
Sheets("SoQuy").Select
Range(Cells(9, 1), Cells(eR, 10)).ClearContents
Rows("9:" & eR).EntireRow.Hidden = False
SoTK = "1111*"
TienTon = 0
With Sheets("NKC")
    If .AutoFilterMode = True Then
        .AutoFilterMode = False
    End If
    eRow = .[I65000].End(xlUp).Row
        Set MyRng = .Range("I9:J" & eRow)
        SoLan = WorksheetFunction.CountIf(MyRng, SoTK)
        Set RngFound = .Cells(9, 10)
        For iL = 1 To SoLan
            With MyRng
               Set RngFound = .Find(SoTK, After:=RngFound, SearchOrder:=xlRows, LookIn:=xlValues, LookAt:=xlWhole)
                    With RngFound
                    Select Case RngFound.Column
                    Case 9
                        iC = 0
                    Case 10
                        iC = -1
                    End Select
                        SoTien = SoTien + .Offset(, 2 + iC).Value
                        If InStr(GhiChu, .Offset(, -5 + iC)) > 0 Then
                            GhiChu = Trim(GhiChu)
                        Else
                            GhiChu = GhiChu & .Offset(, -5 + iC) & ";"
                        End If
                        If InStr(TkDu, .Offset(, 1 + iC)) > 0 Then
                            TkDu = TkDu
                        Else
                            TkDu = TkDu & .Offset(, 1 + iC) & ";"
                        End If
                        If .Offset(, -6 + iC) <> .Offset(1, -6 + iC) Then
                            iR = iR + 1
                            Cells(iR, 1) = iR - 8
                            Cells(iR, 3 - iC) = .Offset(, -6 + iC) 'SoCT'
                            Cells(iR, 2) = .Offset(, -4 + iC) 'NgayHT'
                            If Cells(iR, 2) <> Cells(iR - 1, 2) Then 'Lay ton quy'
                                Cells(iR - 1, 9) = TienTon + Cells(8, 9)
                            End If
                            Cells(iR, 5) = .Offset(, -1 + iC) & "-" & .Offset(, -2 + iC) 'DienGiai'
                            Cells(iR, 6) = TkDu 'TkDu'
                            Cells(iR, 7 - iC) = SoTien 'So Tien Thu chi'
                            Cells(iR, 10) = Left(GhiChu, Len(GhiChu) - 1) 'Note'
                            TienTon = TienTon + SoTien * IIf(iC = 0, 1, iC)
                             SoTien = 0: TkDu = "": GhiChu = ""
                        End If
               End With
            End With
        Next iL
        Cells(iR, 9) = TienTon + Cells(8, 9)
End With
Rows(iR + 2 & ":" & eR).EntireRow.Hidden = True
Set LastCell = Nothing: Set RngFound = Nothing: Set MyRng = Nothing
With Application
        .EnableEvents = True:       .DisplayAlerts = True:       .ScreenUpdating = True:      .Calculation = xlCalculationAutomatic
End With
End Sub

Xin gởi kèm file.
Cám ơn nhiều.

 

[ThuNghi]

Find lấy dữ liệu từ fDate -> eDate!

Theo yêu cầu của...
Tôi làm 1 ví dụ trích lọc dữ liệu từ ngày đầu -> ngày cuối bằng Find Method.
Mong các bạn góp ý. Chưa biết có nhanh hơn AdFi. Các bạn test thử.
Trên đây tạm bỏ qua về sai định dạng, hay là eDate < fDate.
Xin cám ơn!

Sub FindDate()
Dim fDate As Date, eDate As Date, iDate As Date
Dim RngFound As Range, MyRng As Range
Dim eRow As Long, SoLan As Long, iD As Long, iL As Long
Sheet2.Select
Range(Cells(9, 1), Cells(100, 3)).ClearContents
fDate = Cells(6, 1): eDate = Cells(6, 2)
With Sheet1
    .AutoFilterMode = False
    eRow = .[A65000].End(xlUp).Row
    Set MyRng = .Range("A1:A" & eRow)
End With
For iD = fDate To eDate
iDate = CDate(iD) 'Cdate chuyển từ string sang date'
'*** Vấn đề tìm theo Date này có nhiề phần chưa tìm ra
'***Có  thể  ta dùng  iDate=Format(iD,  "Short Date") lúc này phải khai  Dim  iD as  string' 
With Sheet1
    SoLan = WorksheetFunction.CountIf(MyRng, iDate)
'***SoLan này này chính là số lần FindNext.'
    eRow = [a1000].End(xlUp).Row
    Set RngFound = .Cells(1, 1)
    For iL = 1 To SoLan
'***Nên thêm MyRng.number.format="m/d/yyyy" '
'Dự trù MyRng định dạng khác iD'
        With MyRng
           Set RngFound = .Find(iDate, After:=RngFound, SearchOrder:=xlColumns, LookIn:=xlValues, LookAt:=xlWhole)
           With RngFound
            Cells(iL + eRow, 1) = .Value
            Cells(iL + eRow, 2) = .Offset(, 1).Value
            Cells(iL + eRow, 3) = .Offset(, 2).Value
           End With
        End With
    Next iL
End With
Next
Set RngFound = Nothing: Set MyRng = Nothing
End Sub

Code trên là:
1/ Đưa định dạng 2 Range ngày về cùng format
2/ Duyệt từ ngày đầu -> ngày cuối. =>iD
3/ Tìm SoLan xuất hiện iD trong MyRng.
4/ FindNext (SoLan)
5/ Gán vào ....
Từ đó ta có thể vận dụng để tính dd/mm/yy hh:mm
Quan trọng nhất là định dạng.
Đang nghiên cứu tiếp phần format ngày, nó hơi khó hiểu. Đang dừng lại với việc nghiên cứu file Bác Sa trả lời BoyXin.
Các bạn hỗ trợ nhé.

 

[hoangdanh282vn]

Anh viết hay lắm, nhưng nếu có thêm diễn giải Code để minh họa thì tuyệt vời.

Theo em thì Trong Code ta nên Insert thêm một Sheet tạm, Copy dữ liệu qua, Sort dữ liệu theo ngày tháng và trích lọc rồi xóa sheet tạm này đi.

From Sa_DQ: Đúng là cần diễn giải thêm, vì trong excel người ta có thể định dạng dữ liệu ngày tháng bằng rất nhiều cách khác nhau. Nếu không định dạng theo khuôn mẫu, không thể tìm ra

Bác SA ơi, anh Kiệt ơi ! các anh đâu rồi mà sao không thấy bài viết của các anh ạ.

Với cả nhóm: Theo mình thì Topic này vẫn là bản thảo của nhóm mà thôi; Nếu đồng ý như vậy, thì bài mở đầu của topic chính thức tổng hợp về phương thức FIND() sẽ của Ca_Dafi;
Ca_Dafi cho bản thảo lần cuối đi (Tất nhiên hoàn toàn Việt hóa đi - không dùng song ngữ được!)
Bài thứ hai, ba. . . theo dàn bài của Ca_dafi sẽ là của HoangDanh & ThuNghi
Để khỏi phân tâm đọc giả, chúng ta sẽ xóa dần cái topic phụ, kể cả cái này trong nay mai!

 

[ThuNghi]

Vận dụng ý tưởng của HoangDanh282vn, SearchDirection:=xlPrevious, mình viết thử 1 UDF tìm số ct max theo thuật toán dò ngược.
Cú pháp:
MaxSoCT(MyRng As Range, iM As Long, lCtu As String)
MyRng: Vùng chứa số CT.
iM: Tháng cần lấy số CT
lCtu: Loại CT: PT, PN, PX...

Public Function MaxSoCT(MyRng As Range, iM As Long, lCtu As String)
''*** SoCT co dang la PTmm/00i ****''
Dim RngFound As Range
Dim eR As Long, SoCT As String, Dem As Long, iSoCT As String
SoCT = lCtu & Format(iM, "00") & "/"
eR = MyRng.Rows.Count
If eR = 0 Then Exit Function
Dem = WorksheetFunction.CountIf(MyRng, SoCT & "*")
If Dem = 0 Then
    iSoCT = SoCT & "001"
    GoTo bien
End If
Set RngFound = MyRng(1)
With MyRng
    Set RngFound = .Find(SoCT & "*", After:=RngFound, SearchOrder:=xlColumns, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
End With
iSoCT = SoCT & Format(Right(RngFound, 3) + 1, "000")
bien:
MaxSoCT = iSoCT
End Function

Các bạn hoàn thiện hộ. Xin cho ví dụ để hòan thiện Find, tạo vd lâu hơn tạo code.
Cám ơn!

 

[Mr Okebab]

Vận dụng ý tưởng của HoangDanh282vn, SearchDirection:=xlPrevious, mình viết thử 1 UDF tìm số ct max theo thuật toán dò ngược.
Cú pháp:
MaxSoCT(MyRng As Range, iM As Long, lCtu As String)
MyRng: Vùng chứa số CT.
iM: Tháng cần lấy số CT
lCtu: Loại CT: PT, PN, PX...

Public Function MaxSoCT(MyRng As Range, iM As Long, lCtu As String)
''*** SoCT co dang la PTmm/00i ****''
Dim RngFound As Range
Dim eR As Long, SoCT As String, Dem As Long, iSoCT As String
SoCT = lCtu & Format(iM, "00") & "/"
eR = MyRng.Rows.Count
If eR = 0 Then Exit Function
Dem = WorksheetFunction.CountIf(MyRng, SoCT & "*")
If Dem = 0 Then
    iSoCT = SoCT & "001"
    GoTo bien
End If
Set RngFound = MyRng(1)
With MyRng
    Set RngFound = .Find(SoCT & "*", After:=RngFound, SearchOrder:=xlColumns, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlPrevious)
End With
iSoCT = SoCT & Format(Right(RngFound, 3) + 1, "000")
bien:
MaxSoCT = iSoCT
End Function

Các bạn hoàn thiện hộ. Xin cho ví dụ để hòan thiện Find, tạo vd lâu hơn tạo code.
Cám ơn!

Thuật toán dò ngược này chỉ có tác dụng khi Dữ liệu đã được Sort theo kiểu tăng dần thôi bác ạ.

Vì vậy, để đúng cho mọi trường hợp thì nên dò tìm tất cả.

Edit
Mà tại sao lại dùng Countif nếu chỉ đếm 1 lần nhỉ ?? Khi không tìm thấy RngFound = Nothing thì có nghĩa countif = 0 rồi.

Thân!

 

[hoangdanh282vn]

Function tìm địa chỉ ô cuối cùng có chứa dữ liệu trong một sheet nào đó :

Function LastCell(StWs As String) As String
Dim LastRowCell as long, LastColCell as long
On Error Resume Next
With Sheets(StWs)
LastRowCell = .Cells.Find(What:="*", _
                SearchDirection:=xlPrevious, _
                SearchOrder:=xlByRows).Row
LastColCell = .Cells.Find(What:="*", _
                SearchDirection:=xlPrevious, _
                SearchOrder:=xlByColumns).Column
LastCell = StWs & "!" & .Cells(LastRowCell, LastColCell).Address
End With
End Function