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]

Thêm tùy chọn cho đầy đủ hơn

Function LastCell(StWs As String, Optional Opt As Byte = 0) As Variant
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
Select Case Opt
    Case 0: LastCell = StWs & "!" & .Cells(LastRowCell, LastColCell).Address
    Case 1: LastCell = LastRowCell
    Case 2: LastCell = LastColCell
End Select
End With
End Function

 

[t.yen]

Cho em hỏi chút: em có một file excel danh sách các công ty gồm 2 4 cột: tên công ty, mặt hàng họ mua, địa chỉ, điện thoại. Em đã dùng filter để lọc dữ liệu theo tên công ty hay mặt hàng. Nhưng do danh sách công ty và mặt hàng quá dài. Nên mỗi khi tìm tên công ty hay mặt hàng em lại phải dò danh sách từ trên xuống dưới. Em muốn hỏi có cách nào mà giúp em search nhanh như google ko? Chỉ cần gõ tên hay mặt hàng là hiện ra hết!

 

[vbavn]

Thêm tùy chọn cho đầy đủ hơn

Function LastCell(StWs As String, Optional Opt As Byte = 0) As Variant
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
Select Case Opt
    Case 0: LastCell = StWs & "!" & .Cells(LastRowCell, LastColCell).Address
    Case 1: LastCell = LastRowCell
    Case 2: LastCell = LastColCell
End Select
End With
End Function

Trong hàm trên, nếu chúng ta dùng

On Error Resume Next

Liệu có ảnh hưởng đến kết quả của hàm hay không?

Vbavn

 

[SA_DQ]

Tìm kiếm dữ liệu trong cột có các ô trộn(#I)

Tìm kiếm trong cột dữ liệu có các ô trộn​

Trước tiên xin cảm ơn bạn có biệt danh HoangMinhTien trên GiaiPhapExcel.COM đã đưa ra câu hỏi tại http://giaiphapexcel.com/forum/showthread.php?t=19279, để mới có bài này, mà tác giả tạm gọi là tìm kiếm dữ liệu trong vùng có những ô bị trộn chung.
Ba vấn đề bạn ấy hỏi, đều là những vấn đề, mà theo tôi phương thức tìm kiếm trong GPE.COM ta đề cập đến chưa đầy đủ lắm.

Vấn đề I: Tìm và lọc lấy dữ liệu của 1 ngày riêng biệt.

Về vấn đề tìm dữ liệu thể hiện 1 ngày cụ thể trong năm nào đó, ta đã thấy nếu trong bài #16 của http://giaiphapexcel.com/forum/showthread.php?t=15116 do ThuNghi chủ xướng.

Để tiếp tục tìm hiểu về vấn đề tìm kiếm ngày trong cột dữ liệu đã trộn ô, các bạn hãy cùng tôi xem xét các câu lệnh được đánh từ số 1 cho đến trước câu lệnh được đánh số 2 trong macro được dẫn ra dưới đây.

Trước tiên nói về câu lệnh 13:

 Sh.Cells(Zz + 1, "i") = 0

Như trước dòng lệnh 1, ta đã biết biến Zz đang chứa dữ liệu biểu thị dòng cuối của sheets(“Data”) (Các bạn xem thêm trong file đính kèm); Ở đây ta phải thêm giá trị 0 vô cuối cột dữ liệu, để đề phòng trường hợp các ô cuối của cột ‘I’ cần tìm này đã bị trộn nhau lại. Mà ta biết rằng, giả dụ Zz đang bằng 9 & và các ô từ ‘I7:I9’ đã được trộn vô nhau, thì 2 ô ‘I8:I9’ sẽ là 2 ô không chứa trị. Việc này sẽ dẫn chúng ta cùng với excel tưởng rằng dòng ‘7:7’ đã là dòng cuối cùng của dữ liệu. (Thực ra tại ‘B9’ vẫn có dữ liệu, có nghĩa dòng ‘9:9’ mới là dòng cuối chứa dữ liệu)

Chúng ta tiếp. Yêu cầu của bạn HoangMinhTien là nếu dữ liệu ngày cần tìm đã được trộn vào nhau giữa 2, hay 3, . . . dòng trở lên thì phải chép hết chúng sang sheets báo cáo đang có tên là ‘Cau1’, vậy phát sinh vấn đề, cần tìm xem ngày cần tìm đó có 1 hay nhều dòng dữ liệu.

Muốn vậy, ta phải nhờ đến phương thức END(xlDown) trong câu lệnh được đánh số 16 để tìm ra số ô trong cột đã được trộn.

Trên trang tính ‘Data’, nếu 3 ô đã trộn ‘I2:I4’ được ta chọn; tiếp theo ta bấm {STRL} & nhấn kết hợp phím mũi tên xuống, ta sẽ đến được ô ‘I5’

Nhưng khi ta chọn ‘G2’, thậm chí cả 3 ô ‘G2:G4’, sau đó bấm {STRL} & kết hợp phím mũi tên xuống ta sẽ rơi vô ô ‘G9’ là ô cuối của cột ‘G:G’ liên tục có chứa dữ liệu

Điều đó nói lên rằng trong ‘I7:I9’ ta chỉ có ‘I7’ chứa dữ liệu mà thôi. (Nếu sau ‘I9’ không còn dữ liệu, ta sẽ đến ô cuối của cột ‘I:I’ này. (Điều này nói rõ hơn việc chúng ta sao lại phải thêm vô ‘I10’ giá trị 0 )

Trong macro dưới đây, tại [I2] ta có thể thay bằng [I3] hay [I4] cũng chỉ có cùng kết quả.

[B]Sub MuiTenXuongMergesCells()[/B]
 [I2].Select:	                            MsgBox Selection.End(xlDown).Address
[B]End Sub[/B]

Trở lại với công cuộc chủ yếu của chúng ta. Các dòng lệnh 17 & 18 dùng để chép các dòng dữ liệu của ngày hôm đó sang sheets báo cáo.

Còn các dòng lệnh từ 19,100 . . . cho đến 109 là trộn các ô trên trang báo cáo cho giống với trang ‘Data’ theo yêu cầu của ‘Thượng đế’ HoangMinhTien mà thôi!

 

[SA_DQ]

Tìm kiếm dữ liệu trong cột có các ô trộn(#2)

Tìm kiếm nhiều dòng dữ liệu, trong đó có một số dòng được trộn.​

Chúng ta sẽ đề cập đến nhiệm vụ cần tìm & chép toàn bộ các dòng chứa dữ liệu tại sheets(“Data”) mà trên cột ‘M:M’ đang có trị ‘NG’ hay ‘OK’. Hơn nữa, một số trong các dòng dữ liệu này đã trộn (merge) tại một số trường.

Macrro sẽ thực thi các dòng lệnh từ dòng mang số 2 cho tới trước dòng mang số 3, một khi ta nhập vô ô [D4] một trong các trị nêu trên.

Chúng ta biết chắc 1 điều rằng, trị cần tìm có nhiều trong cột ‘M:M’, nên chúng ta, khác với lần tìm kiếm trị ngày bên trên, cần sử dụng biến MyAdd có dạng chuỗi để ghi lại địa chỉ ô chứa trị ‘NG’ hay ‘OK’ đầu tiên tìm được. Điều này giúp chúng ta thoát khỏi vòng lẫn quẫn hoài trong vòng lặp khi đã xong nhiệm vụ tìm kiếm. (Điều kiện để thoát vòng lặp được ghi tại dòng 211.)

Trình tự các bước trong khi tìm thấy 1 record tại ‘Data’ như sau:
• Gán địa chỉ ô được tìm thấy đầu tiên vô biện kiểu chuỗi;
1. Xác định số dòng mà các records có cùng ngày & cùng “JUDGMENT”. (Các ô được trộn chung trong các trường, trừ [PO] & [QTY]
2 Chép các dòng này sang sheets(“Cau1”) để lập báo cáo
3 Trộn các ô cần trộn ở ‘Cau1’ cho giống với dữ liệu bên ‘Data’
Quá trình tìm được lặp lại cho đến khi nào vẫn thỏa điều kiện, được ghi tại dòng lệnh 211

Trước đây ta hay làm bằng cách khác. Đó là chép record được tìm thấy vô biến kiểu Range đã khai báo trước, như sau:

Dim cRng As Range, MyAdd As String
. . . .
Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        MyAdd = sRng.Address
        Do
	If cRng Is Nothing Then
	    Set cRng = sRng
          Else
              Set cRng = Union(cRng, sRng)	
          End If
          Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    End If
    cRng.Copy Destination:= Sheets(“Cau1”).[A. .]

Làm như thông lệ như vậy sẽ nhanh hơn cách mà chúng ta chép từng dòng như trên. Nhưng ở đây, ta phải còn thực thi đòi hỏi của ‘Đấng Tối Cao” là trộn các ô của hầu hết các trường của các records vừa chép sang ‘Cau1’ cho giống y chang bên ‘Data’. Việc định dạng này được khẳng định trong macro bằng các dòng lệnh từ 201..209 tương ứng.

phát sinh câu hỏi, sao chúng ta không chép hết sang như lối mòn truyền thống, sau đó đi định dạng trộn các ô cần thiết?
Câu trả lời có thể là, ‘Việc hôm nay chớ để ngày mai; Nó sẽ hóa bùn mất!”. Một khi ta làm như vậy, lại phải đi xác định ô nào cần trôn với ô nào không cần trộn & quan trọng hơn là bao nhiêu ô trong 1 cột cần thiết phải trộn.

' Đây là macro trong file đính kèm ở bài trên'
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
 On Error GoTo LoiWCh
 Dim Rng As Range, sRng As Range, cRng As Range
 Dim Zz As Long, jRw As Long:                   Dim Sh As Worksheet
 Dim MyAdd As String
 
 Application.ScreenUpdating = False:            Set Sh = Sheets("Data")
 Zz = Sh.[b65500].End(xlUp).Row
1 If Not Intersect(Target, [d3]) Is Nothing Then
    Range("B7").Resize(Zz).EntireRow.Delete
    Set Rng = Sh.Range(Sh.[i1], Sh.Cells(Zz, "i"))
13    Sh.Cells(Zz + 1, "i") = 0
    Set sRng = Rng.Find(Format(Target.Value, "Short Date"), , xlFormulas)
    If Not sRng Is Nothing Then
16       jRw = sRng.End(xlDown).Row - sRng.Row
       Set Rng = [b65500].End(xlUp).Offset(1, -1)
18       Rng.Resize(jRw, 13).Value = sRng.Offset(, -8).Resize(jRw, 13).Value
19       If jRw > 1 Then
100            For Zz = 1 To 13
                Rng.Offset(, Zz - 1).Resize(jRw).Select
                With Selection
                    .VerticalAlignment = xlCenter
                    If Zz <> 2 And Zz <> 7 Then .MergeCells = True
                End With
            Next Zz
106    End If:                                     End If
    Sh.Cells(Zz + 1, "i") = "":                 [d2].Select
    
2 ElseIf Not Intersect(Target, [d4]) Is Nothing Then
    Range("B7").Resize(Zz).EntireRow.Delete
    Set Rng = Sh.Range(Sh.[M1], Sh.Cells(Zz, "M"))
23    Sh.Cells(Zz + 1, "M") = "@"
    Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
26        MyAdd = sRng.Address
        Do
28            jRw = sRng.End(xlDown).Row - sRng.Row
            Set cRng = [b65500].End(xlUp).Offset(1, -1)
200            cRng.Resize(jRw, 13).Value = sRng.Offset(, -12).Resize(jRw, 13).Value
            If jRw > 1 Then
                For Zz = 1 To 13
                    cRng.Offset(, Zz - 1).Resize(jRw).Select
                    With Selection
                        .VerticalAlignment = xlCenter
                        If Zz <> 2 And Zz <> 7 Then .MergeCells = True
                    End With
                Next Zz
            End If
            Set sRng = Rng.FindNext(sRng)
211        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    End If
213    Sh.Cells(Zz + 1, "M") = "":                 [d2].Select
    
3 ElseIf Not Intersect(Target, [d5]) Is Nothing Then
    Range("B7").Resize(Zz).EntireRow.Delete
    Set Rng = Sh.Range(Sh.[M1], Sh.Cells(Zz, "M"))
    Sh.Cells(Zz + 1, "M") = "@"
    Set sRng = Rng.Find(Target.Value, , xlFormulas, xlWhole)
    If Not sRng Is Nothing Then
        MyAdd = sRng.Address
        Do
            jRw = sRng.End(xlDown).Row - sRng.Row
            Set cRng = [C65500].End(xlUp).Offset(1)
            cRng.Resize(, 11).Value = sRng.Offset(, -10).Resize(, 11).Value
            If jRw > 1 Then
                cRng.Offset(, 4).Value = Application.WorksheetFunction _
                    .Sum(sRng.Offset(, -6).Resize(jRw))
            End If
            Set sRng = Rng.FindNext(sRng)
        Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
    End If
    Sh.Cells(Zz + 1, "M") = "":                 [d2].Select
 End If
 Exit Sub
LoiWCh:                                         End Sub

 

[SA_DQ]

Áp dụng phương thức để tìm các ô chứa 1 khoảng dữ liệu số.

Macro dưới đây sẽ tìm và tô màu những ô dữ liệu thỏa điều kiện nằm giữa hai trị (kiểu long) mà ta nhập vào;

Nó tìm & cho biết cả những số thực thỏa điều kiện, kể cả số âm lẫn số dương.

Nhưng chúng ta không thể bắt nó tìm bỡi giới han giữa hai số thực.

Nó cho phép tìm trong toàn bộ trang tính (khi ta kích hoạt ban đầu chỉ 1 ô) hay vùng dữ liệu chọn trước (Tất nhiên vùng này có thể là 1 cột, 1 hàng hay vài cột & vài hàng hoặc hai vùng cách nhau.

Số màu dùng để tô các ô tìm thấy chỉ là 5-7 màu, nên màu nền ở ô tìm thây giống nhau chưa hẵn là trị của chúng giống nhau. (Nếu cần, các bạn có thể phát triển thêm, bằng cách tô màu font khác nhau để phân biệt.)

Option Explicit
Sub FindAllBetween2Nums()
 Dim StrNum As String:                      Dim bColor As Byte
 Dim lMin As Long, lMax As Long, CllsCount As Long
 Dim sRng As Range, Rng0 As Range, CRng As Range, FRng As Range
 
 StrNum = InputBox("Please enter the first value, then a comma, " _
    & vbNewLine & "followed by the second value" & _
    vbNewLine & "E.g. 1,19", "FIND BETWEEN 2 INTEGER")
 If StrNum = vbNullString Then Exit Sub
 On Error Resume Next
 lMin = Left(StrNum, InStr(1, StrNum, ","))
 lMax = Replace(StrNum, lMin & ",", "")
 If lMax < lMin Then
    CllsCount = lMax:                       lMax = lMin
    lMin = CllsCount
 End If
 If lMin + 1 = lMax Then
    MsgBox "No scope between Min and Max", vbCritical, "GPE.COM"
    Exit Sub
 End If
 If Selection.Cells.Count = 1 Then
    Set CRng = Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
    Set FRng = Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)
 Else
    Set CRng = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
    Set FRng = Selection.SpecialCells(xlCellTypeFormulas, xlNumbers)
 End If
 If CRng Is Nothing And FRng Is Nothing Then
    MsgBox "Your Selection contains no numbers", vbCritical, "GPE.COM"
    Exit Sub
 ElseIf CRng Is Nothing Or FRng Is Nothing Then
     Set sRng = IIf(FRng Is Nothing, CRng, FRng)
 Else
    Set sRng = Application.Union(FRng, CRng) 'Both'
 End If
 Set Rng0 = sRng.Cells(1, 1)
 Cells.ClearFormats:                        bColor = 33
 CllsCount = sRng.Cells.Count:
 Do Until CllsCount = 0
    CllsCount = CllsCount - 1
    Set Rng0 = sRng.Cells.Find("*", Rng0, LookIn:=xlValues, lookat:=xlWhole)
    If Rng0.Value > lMin And Rng0.Value < lMax Then
        bColor = bColor + 1:            If bColor = 41 Then bColor = 34
        Rng0.Interior.ColorIndex = bColor
    End If
 Loop
 On Error GoTo 0
End Sub

 

[SA_DQ]

Phương thức FIND() với chuỗi ngày & giờ

Xin xem thêm trong file đính kèm để rõ thêm

Option Explicit
Sub FindDateTime()
    Dim StrDate As String:                  Dim rCell As Range
    Dim lReply As Long, bColor As Integer
     
    Columns("A:A").NumberFormat = "mm/dd/yyyy hh:mm"
    Columns("B:B").NumberFormat = ("MM/dd/YYYY hh:mm:ss")
5    StrDate = Application.InputBox("Enter a  Date & Time to Locate on This  Worksheet", _
    "FIND DATE & TIME", Format(Now, "mm//dd/yyyy hh:mm:ss"), Type:=1)
    
    If StrDate = "False" Then Exit Sub
7    StrDate = Format(StrDate, "mm/dd/yyyy hh:mm:ss")
    On Error Resume Next
9    Set rCell = Cells.Find(CDate(StrDate), , xlFormulas, xlWhole)
    On Error GoTo 0
    If Not rCell Is Nothing Then
        bColor = rCell.Interior.ColorIndex + 1
        If bColor > 41 Or bColor < 34 Then bColor = 34
        rCell.Interior.ColorIndex = bColor
    Else
        lReply = MsgBox("Date & Time cannot be found. Try Again", vbYesNo)
        If lReply = vbYes Then Run "FindDateTime"
    End If
    Columns("A:A").NumberFormat = "dd/mm/yyyy hh:mm"
    Columns("B:B").NumberFormat = ("dd/mm/YYYY hh:mm:ss")

End Sub

Việc gì sẽ sảy ra, nếu ở các dòng lệnh có ghi số sẽ thay đổi như sau:

91    Set rCell = Cells.Find(CDate(StrDate), , xlFormulas, [B]xlPart[/B])

51    StrDate = Application.InputBox("Enter a  Date & Time to Locate on This  Worksheet", _
    "FIND DATE & TIME", Format(Now, "mm//dd/yyyy [B]hh:mm[/B]"), Type:=1)

71    StrDate = Format(StrDate, "mm/dd/yyyy [B]hh:mm[/B]")

 

[SA_DQ]

Tìm kiếm trong vùng có những ô bị ẩn

TÌM KIẾM TRONG VÙNG CÓ NHỮNG Ô BỊ ẨN​

Để tìm hiểu phương thức tìm kiếm trong vùng có một số ô dữ liệu bị ẩn, ta xét đến trang tính có chứa dữ liệu theo như ví dụ bảng sau:

|A|B|C|D|E
1|TT| MaNV |||
2|1|A001|||
3|2|A002|||
6|5|A005|||
7|6|A006||14|=MATCH("A013",B1:B24,0)
8|7|A007|||
9|8|A008||A011|=VLOOKUP(A12,A$1:B27,2)
10|9|A009|||
21|20|A020|||
22|21|A021||A018|=VLOOKUP(18,A$1:B39,2)
23|22|A022|||

Trên trang náy, ta thấy các dòng 4-5 & 11-20 đã bị ẩn đi;

Nếu tại [D7] ta nhập hàm =MATCH("A013",B1:B24,0), kết quả trả về tại đây là ‘14’
(Có nghĩa là hàm MATCH() vẫn xem các ô ẩn cũng như các ô không ẩn.)

Cũng tương tự như vậy với hàm VLOOKUP():
Tại [D9] ta lại dùng công thức =VLOOKUP(A12,A$1:B27,2). Sau khi ta nhấn phím {ENTER} sẽ thấy xuất hiện ‘A011’

Nhưng dùng phương thức FIND() để tìm theo macro sau:

Option Explicit

Sub FINDHideCells()
 Dim Rng As Range, sRng As Range
 
 Set Rng = Range([b1], [b1].End(xlDown))
3  Set sRng = Rng.Find("A018", , xlFValues, xlWhole)
 If sRng Is Nothing Then
    MsgBox "No Find"
 Else
    MsgBox sRng.Address
 End If
End Sub

Chúng ta sẽ nhận được câu trả lời lạnh lùng: Không tìm thấy.

Chuyện sẽ khác hoàn toàn, nếu trong câu lệnh được gán số 3 trên kia, chỉ cần chuyền từ xlValues thành xlFormulas

Bạn nào chưa tin, hãy thử dù 1 lần để tự khẳng định.

 

[ChanhTQ@]

Một trong những điển hình của phương thức FINDPREVIOUS()

http://giaiphapexcel.com/forum/showthread.php?t=20743#6

 

[mymichau]

Option Explicit
Sub FINDHideCells()
 Dim Rng As Range, sRng As Range
 Set Rng = Range([b1], [b1].End(xlDown))
3 Set sRng = Rng.Find("A018", , xlFValues, xlWhole)
If sRng Is Nothing Then
         MsgBox "No Find"
Else
        MsgBox sRng.Address
End If
End Sub

Với Macro trên, khi em thay xlComments, xlWhole thì không tìm được trong Comment, nhưng khi thay xlWhole thành xlPart lại hoạt động tốt.
Theo em hiểu xlWhole là tìm toàn bộ, còn xlPart là tìm một phần.
Vậy vì sao tìm trong Comments lại phải dùng phương thức tìm một phần? Nhờ các bạn giải thích giùm. Xin cảm nơn nhiều.

 

[SA_DQ]

Quả thật mình cũng mới tìm hiểu về vấn đề này; Sau đây là 1 số kết quả ban đầu

Sub FINDComments()
 Dim Rng As Range, sRng As Range, MyAdd As String
 
 Set Rng = Range([b1], [c2].End(xlDown))
2 Set sRng = Rng.Find("*", , xlComments, xlWhole)
 If sRng Is Nothing Then
    MsgBox "No Find"
 Else
    MyAdd = sRng.Address
    Do
        MsgBox sRng.Address:                ConNhieuThuKhacNua
6        sRng = Rng.FindNext(sRng)
    Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
 End If
End Sub

[B]Sub ConNhieuThuKhacNua()[/B]
 Dim Comm As Comment
 
 For Each Comm In ActiveSheet.Comments
    If Comm.Author = "GPE.COM" Then
        Comm.Delete
    Else
        MsgBox Comm.Text, , Comm.Author
    End If
 Next
[B]End Sub[/B]

Thứ nhất, mình thấy rằng, thêm vô chổ findWhat:= "*" thì ta có thể xài được thông số xlWhole mà macro không đi sai hướng đã định;
Thứ hai: Thay vì mình muốn tìm tất cả các ô chứa Comments thì macro đầu chưa cho ta thỏa, chắc phải tìm cách khác . . .
Nhưng khi gọi macro thứ hai, thì nó liệt kê tất tần tật các Comments có trên trang tính đang được kích hoạt

(Có thể trả lời này không đủ thỏa đáng với bạn, mong được thông cảm & cùng nhau, chúng ta ngâm cứu tiếp)

 

[SA_DQ]

Trích dữ liệu từ cột số gồm 5 hay 6 chữ số với yêu cầu có số hàng chục giống nhau

Ví dụ cần rút trích để phân làm 2 loại (được chia theo 2 màu xanh & đỏ) như sau:

|C|D|E|. . .
| 091281 |HJCU|2154254|
| 090201 |HJCU|2154954|
| 101282 |HJCU|2854254|
| 111208 |HJCU|2954254|
| 901200 |HJCU|2154999|. . .

Đây là yêu cầu của 1 bạn để giải quyết bài toán hoàn toàn có trong thực tế:
http://giaiphapexcel.com/forum/showthread.php?p=174537#post174537

Tại topic đó, tôi đã giải quyết bằng cách dùng vòng lặp duyệt từ đầu đến cuối; Hễ gặp trị số nào thỏa điều kiện thì rút trích sang trang tính bên cạnh; Đến lượt nó, sẽ là nguồn để tạo hai bảng báo cáo riêng biệt trên trang tính thứ ba (Xin các bạn xem thêm trong topic đó để rõ hơn)

Nhưng do biết dùng vòng lặp sẽ chậm hơn phương thức FIND(), nên sau một thời gian tiếp tục nghiên cứu hoàn thiện, xin giới thiệu với các bạn sử dụng phương thức FIND() để thực thi nhiệm vụ chắc chắn là nhanh hơn phương cách dùng vòng lặp trên.
Sau đây là macro (Xin các bạn xem file đính kèm):

Chú ý: Hai nút để kích hoạt macro đang là [L5] & [L35] để thay dữ liệu trong từng báo cáo tương ứng

[B]Option Explicit[/B]
Private Sub Worksheet_Change(ByVal Target As Range)
'   '
 If Not Intersect(Target, [L5]) Is Nothing Then
   [c7].Resize(15, 3).ClearContents
   RutTrich "*0?", Target.Value
 ElseIf Not Intersect(Target, [L35]) Is Nothing Then
   [c37].Resize(15, 3).ClearContents
   RutTrich "*8?", Target.Value
 End If
[B]End Sub[/B]

Sub RutTrich(StrC, Cot5)
 Dim Sh As Worksheet, Sht As Worksheet, Rng As Range, sRng As Range
 Dim MyAdd As String:                        Dim Num2 As Byte
 
 Set Sh = Sheets("DuLieu"):                  Set Sht = Sheets("TrG")
 Set Rng = Sh.Range(Sh.[c3], Sh.[c65500].End(xlUp))
 Sht.[b4].Resize(Rng.Rows.Count, 11).ClearContents
 Num2 = CInt(Left(Right(StrC, 2), 1))
 Set sRng = Rng.Find(StrC, , xlFormulas, xlPart)
 If Not sRng Is Nothing Then
   MyAdd = sRng.Address
   Do
      With sRng
         If (.Value \ 10) Mod 10 = Num2 And .Value / 10 ^ 4 >= 1 Then
            Sht.[b65500].End(xlUp).Offset(1).Resize(, 11).Value = _
               sRng.Offset(, -1).Resize(, 11).Value
         End If
      End With
      Set sRng = Rng.FindNext(sRng)
   Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
 End If
 Set Sh = Nothing
 Set Rng = Switch(Num2 = 8, [c37], Num2 = 0, [c7])
 Rng.Resize(15, 3).Value = Sht.Cells(4 + 15 * (Cot5 - 1), "C").Resize(15, 3).Value
End Sub

 

[pmhoang]

Find bị lỗi, không tìm thấy Cell chứa giá trị Max

Mình viết Code này có tác dụng như sau:

1. Ở cột A, cứ mỗi loại T1,T2,T3…, thì lấy giá trị M2max, M3max gáng cho cột DJ, DK
2. Tô màu hàng được lấy M2max, M3max
3. Gáng tên của cột B, tương ứng với M2max, M3max vào cột DA

Hỏi GPE
Mình làm code làm được nhiệm vụ 1, còn nhiệm vụ 2, 3 mình làm code theo suy nghĩ là phù hợp, nhưng nó cứ gặp lỗi ở phương thức Find.
Mong các bạn giúp đở, cảm ơn các bạn GPE nhiều.

'-- Cap nhat lai C1 max, C2 max
Sub Click_2()
Dim VungAs2 As Range
Dim Cell_M2max As Range
Dim Cell_M3max As Range
Dim eRow As Long
Dim i As Long
Dim n As Integer
Dim M2max As Double '-- Dung dang bien Double ko biet co hop ly ko???
Dim M3max As Double
'--**************************
Range([A7], [CP65536]).Interior.ColorIndex = xlNone
n = 5
eRow = [A65536].End(xlUp).Row
Set VungAs2 = [CO7] 'Vung de chon gia tri M2max
For i = 6 To eRow
  Set VungAs2 = Application.Union(Cells(i, "CO"), VungAs2)
  
  If Cells(i, "A").Value <> Cells(i + 1, "A").Value Then
    n = n + 1
    '-- Lay gia tri Max cua VungAs
    M2max = Application.Max(VungAs2)
    M3max = Application.Max(VungAs2.Offset(, 1))
    Cells(n, "DJ").Value = M2max
    Cells(n, "DK").Value = M3max
    
    '-- Tim Cells tai vi tri Max(VungAs2)
    '-- ??? --- Tai sao Find khong tim thay Cell nao vay, da so la tra ve gia tri Nothing???
    Set Cell_M2max = VungAs2.Find(What:=M2max, LookIn:=xlValues, LookAt:=xlWhole)
    Set Cell_M3max = VungAs2.Offset(, 1).Find(What:=M3max, LookIn:=xlValues, LookAt:=xlWhole)
    
    '-- To mau cho hang co M2max, M3max
    Range(Cells(Cell_M2max.Row, "A"), Cells(Cell_M2max.Row, "CP")).Interior.ColorIndex = 40
    Range(Cells(Cell_M3max.Row, "A"), Cells(Cell_M3max.Row, "CP")).Interior.ColorIndex = 40
    
    '-- Gang ten tim duoc vao cot "DA"
    Cells(n, "DA").Value = Cells(Cell_M2max.Row, "B").Value & "-" & Cells(Cell_M3max.Row, "B").Value
    '-- Bat dau lai VungA2 moi, cho cot A moi
    Set VungAs2 = Cells(i + 1, "CO")
  End If
Next
'--**************************
End Sub

Có file gởi kèm.

 

[ndu96081631]

Mình viết Code này có tác dụng như sau:

1. Ở cột A, cứ mỗi loại T1,T2,T3…, thì lấy giá trị M2max, M3max gáng cho cột DJ, DK
2. Tô màu hàng được lấy M2max, M3max
3. Gáng tên của cột B, tương ứng với M2max, M3max vào cột DA

Hỏi GPE
Mình làm code làm được nhiệm vụ 1, còn nhiệm vụ 2, 3 mình làm code theo suy nghĩ là phù hợp, nhưng nó cứ gặp lỗi ở phương thức Find.
Mong các bạn giúp đở, cảm ơn các bạn GPE nhiều.

'-- Cap nhat lai C1 max, C2 max
Sub Click_2()
Dim VungAs2 As Range
Dim Cell_M2max As Range
Dim Cell_M3max As Range
Dim eRow As Long
Dim i As Long
Dim n As Integer
Dim M2max As Double '-- Dung dang bien Double ko biet co hop ly ko???
Dim M3max As Double
'--**************************
Range([A7], [CP65536]).Interior.ColorIndex = xlNone
n = 5
eRow = [A65536].End(xlUp).Row
Set VungAs2 = [CO7] 'Vung de chon gia tri M2max
For i = 6 To eRow
  Set VungAs2 = Application.Union(Cells(i, "CO"), VungAs2)
  
  If Cells(i, "A").Value <> Cells(i + 1, "A").Value Then
    n = n + 1
    '-- Lay gia tri Max cua VungAs
    M2max = Application.Max(VungAs2)
    M3max = Application.Max(VungAs2.Offset(, 1))
    Cells(n, "DJ").Value = M2max
    Cells(n, "DK").Value = M3max
    
    '-- Tim Cells tai vi tri Max(VungAs2)
    '-- ??? --- Tai sao Find khong tim thay Cell nao vay, da so la tra ve gia tri Nothing???
    Set Cell_M2max = VungAs2.Find(What:=M2max, LookIn:=xlValues, LookAt:=xlWhole)
    Set Cell_M3max = VungAs2.Offset(, 1).Find(What:=M3max, LookIn:=xlValues, LookAt:=xlWhole)
    
    '-- To mau cho hang co M2max, M3max
    Range(Cells(Cell_M2max.Row, "A"), Cells(Cell_M2max.Row, "CP")).Interior.ColorIndex = 40
    Range(Cells(Cell_M3max.Row, "A"), Cells(Cell_M3max.Row, "CP")).Interior.ColorIndex = 40
    
    '-- Gang ten tim duoc vao cot "DA"
    Cells(n, "DA").Value = Cells(Cell_M2max.Row, "B").Value & "-" & Cells(Cell_M3max.Row, "B").Value
    '-- Bat dau lai VungA2 moi, cho cot A moi
    Set VungAs2 = Cells(i + 1, "CO")
  End If
Next
'--**************************
End Sub

Có file gởi kèm.

Cái này dùng Consolidate chỉ có vài dòng code là ra, ai lại Find rồi For chi cho cực vậy
Consolidate bạn làm bằng tay còn được nữa là...

 

[pmhoang]

Cái này dùng Consolidate chỉ có vài dòng code là ra, ai lại Find rồi For chi cho cực vậy
Consolidate bạn làm bằng tay còn được nữa là...

Mình sẽ nguyên cứu lại xem, vì hiện giờ mình vẫn không biết Consolidate là gì, và dùng như thế nào nữa.
Hiện tại mong bạn nào đó giúp mình khắc phục cái lỗi find này với.cám ơn các bạn.

 

[ndu96081631]

Mình sẽ nguyên cứu lại xem, vì hiện giờ mình vẫn không biết Consolidate là gì, và dùng như thế nào nữa.
Hiện tại mong bạn nào đó giúp mình khắc phục cái lỗi find này với.cám ơn các bạn.

Rất dể xài ---> Gữi bạn file ví dụ (có hình minh họa)
Nếu bạn làm bằng tay thành công, hãy record macro quá trình bạn thao tác rồi chỉnh lại tí code là xong!

 

[pmhoang]

Rất dể xài ---> Gữi bạn file ví dụ (có hình minh họa)
Nếu bạn làm bằng tay thành công, hãy record macro quá trình bạn thao tác rồi chỉnh lại tí code là xong!

1. Em đã record macro lại, nhưng cũng không làm được gì với cái record đó, vì trong record nó có địa chỉ tuyệt đối của file, như thế nếu chép file vào chổ khác sẽ không chạy được (em có bỏ cái đĩa chỉ đó thử, ai gì bị lỗi) -> bó tay.
2. Consolidate không giải quyết được ý thứ 2 hoăc ý thư 3, Mục đích của mình là xác định được hàng nào chứa M2max, M3max (tô màu hàng đó) và để lấy giá trị cột B (B) gắng cho B (DA) tương ứng.
3. Không biết em hỏi bài ở đây có hợp không? Hay là mở một post mới

 

[SA_DQ]

Hiện tại mong bạn nào đó giúp mình khắc phục cái lỗi find này với.cám ơn các bạn.

Câu (1) bạn viết vậy là tốn thời gian
Mình làm cho bạn câu (1) & câu (2) như dưới đây, bạn chép toàn bộ thay cho cái macro của bạn; Chú í mình ghi kết quả trên các cột kề phải cột của bạn đã ghi kết quả; Bạn hãy sửa lại theo í của bạn.

Thêm 1 chú í về tên của macro đang lệnh nhau nhiều, bạn sửa lại luôn đi nhe!

Câu (3) chưa hiểu lắm, nên chưa làm

Sub TimMax()
 Dim Clls As Range, Rng As Range, sRng As Range
 Dim Rngi As Range, mRng As Range, Rngs As Range
 Dim MyAdd As String
1 ' Tìm Giá Tri Cuc Dai:'
 Set Rng = Range([a6], [A65500].End(xlUp))
 For Each Clls In Range("Cz7:CZ" & [CZ99].End(xlUp).Row)
   Set Rngi = [co5]
   Set sRng = Rng.Find(Clls.Value, , xlFormulas, xlWhole)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
      Do
         Set Rngi = Union(Rngi, Cells(sRng.Row, "CO"))
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
      With Cells(Clls.Row, "DL")
         .Value = Application.WorksheetFunction.Max(Rngi)
         .Offset(, 1).Value = Application.WorksheetFunction.Max(Rngi.Offset(, 1))
      End With
   End If
2 ' To Màu Các Ô Cuc Dai:'
   Set Rngs = Rngi.Find(Cells(Clls.Row, "DL").Value)
   If Not Rngs Is Nothing Then
      Cells(Rngs.Row, "A").Resize(, 9).Interior.ColorIndex = 34 + (Clls.Row Mod 6)
      Set Rngs = Nothing
   End If
   Set Rngs = Rngi.Offset(, 1).Find(Cells(Clls.Row, "DM").Value)
   If Not Rngs Is Nothing Then
      Cells(Rngs.Row, "A").Resize(, 9).Interior.ColorIndex = 42 - (Clls.Row Mod 6)
      Set Rngs = Nothing
   End If   
   If Not Rngi Is Nothing Then Set Rngi = Nothing
 Next Clls
End Sub

 

[ndu96081631]

2. Consolidate không giải quyết được ý thứ 2 hoăc ý thư 3, Mục đích của mình là xác định được hàng nào chứa M2max, M3max (tô màu hàng đó) và để lấy giá trị cột B (B) gắng cho B (DA) tương ứng.

trời ơi... tô màu sao không dùng Conditional Formating cho gọn --> Việc gì phải viết code!
File của bạn sao tôi thấy nó tùm lum quá, chẳng biết đường nào mà lần cả ---> Cả đóng cột ẩn và dòng ẩn mà chẳng có tí dử liệu nào

 

[pmhoang]

Câu (1) bạn viết vậy là tốn thời gian
Mình làm cho bạn câu (1) & câu (2) như dưới đây, bạn chép toàn bộ thay cho cái macro của bạn; Chú í mình ghi kết quả trên các cột kề phải cột của bạn đã ghi kết quả; Bạn hãy sửa lại theo í của bạn.

Thêm 1 chú í về tên của macro đang lệnh nhau nhiều, bạn sửa lại luôn đi nhe!

Câu (3) chưa hiểu lắm, nên chưa làm

Code của bạn đúng như ý mình, mình đã chỉnh lại ý số 2. và để cho nó làm được ý 3 rồi này:

Sub TimMax()
 Dim Clls As Range, Rng As Range, sRng As Range
 Dim Rngi As Range, mRng As Range, Rngs2 As Range, Rngs3 As Range
 Dim MyAdd As String
1 ' Tìm Giá Tri Cuc Dai:'
 Set Rng = Range([a6], [A65500].End(xlUp))
 For Each Clls In Range("Cz7:CZ" & [CZ99].End(xlUp).Row)
   Set Rngi = [co5]
   Set sRng = Rng.Find(Clls.Value, , xlFormulas, xlWhole)
   If Not sRng Is Nothing Then
      MyAdd = sRng.Address
      Do
         Set Rngi = Union(Rngi, Cells(sRng.Row, "CO"))
         Set sRng = Rng.FindNext(sRng)
      Loop While Not sRng Is Nothing And sRng.Address <> MyAdd
      With Cells(Clls.Row, "DL")
         .Value = Application.WorksheetFunction.Max(Rngi)
         .Offset(, 1).Value = Application.WorksheetFunction.Max(Rngi.Offset(, 1))
      End With
   End If
2 ' To Màu Các Ô Cuc Dai:'
   Set Rngs2 = Rngi.Find(Cells(Clls.Row, "DL").Value)
   If Not Rngs2 Is Nothing Then
      Cells(Rngs2.Row, "A").Resize(, 9).Interior.ColorIndex = 34 + (Clls.Row Mod 6)
   End If

   Set Rngs3 = Rngi.Offset(, 1).Find(Cells(Clls.Row, "DM").Value)
   If Not Rngs3 Is Nothing Then
      Cells(Rngs3.Row, "A").Resize(, 9).Interior.ColorIndex = 42 - (Clls.Row Mod 6)
   End If
   If Not Rngi Is Nothing Then Set Rngi = Nothing
3 ' Gang ten cho cot DA
   Cells(Clls.Row, "DA").Value = Cells(Rngs2.Row, "B").Value & "-" & Cells(Rngs3.Row, "B").Value
 Next Clls
End Sub

Tuy là đã làm được điều mình cần làm, nhưng nhìn thuật toán của bạn, bạn cũng dùng find như mình, cách bạn dùng là:

Set Rngs2 = Rngi.Find(Cells(Clls.Row, "DL").Value)

Cách mình dùng là: (bị lỗi không tìm thấy Cell_M2max)

Set Cell_M2max = VungAs2.Find(What:=M2max, LookIn:=xlValues, LookAt:=xlWhole)

Mình thử làm theo cách của bạn là bỏ đi các phần đằng sau cua find

Set Cell_M2max = VungAs2.Find(What:=M2max)

Như thế này lại chạy ok, không biết phải lý giải như thế nào nửa???
???khó hiểu quá Hyên7 oi?