Option Explicit
Sub FindMultiCol_to_ListBox(ByVal findvalue, ByVal ignorecase As Boolean, ByVal matchstr As String, data, ListBox As Object, ParamArray cols())
' - tim findvalue trong mang 2 chieu data, ket qua nhap vao ListBox. Truoc khi tim thi cac muc trong ListBox duoc xoa.
' - findvalue: gia tri can tim
' - data: mang 2 chieu
' - ListBox: ListBox voi cac muc duoc tim thay
' - cols la danh sach chi so cac cot can tim trong mang data duoc phan cach boi dau phay. Cac chi so cot phai duoc tinh
' tu 1 cho du chi so cot trong mang duoc tinh tu dau. Vd. mang data co chi so cot duoc tinh tu 3 den 7 vd. data(1 to 100, 3 to 7)
' thi neu ta muon tim kiem trong cot dau tien cua mang data thi ta truyen 1 chu khong phai la truyen 3.
' Neu tim tat ca cac cot cua mang data thi ngoai cach liet ke chi so cua tat ca cac cot cung co the khong truyen cols.
' Tuc neu bo qua cols thi hieu la tim tat ca cac cot
' ignorecase = True la khong phan biet chu hoa chu thuong
' matchstr = "" la tim dung
' matchstr = "<" la tim voi findvalue la doan dau
' matchvalue = ">" la tim voi findvalue la doan cuoi
' matchstr khac "", "<", ">" la tim voi findvalue o vi tri bat ky
Dim r As Long, k As Long, count As Long, c, value_, chiso, result()
ListBox.Clear
On Error GoTo end_
If ignorecase Then findvalue = LCase(findvalue)
If matchstr = "<" Then
findvalue = findvalue & "*"
ElseIf matchstr = ">" Then
findvalue = "*" & findvalue
ElseIf matchstr <> "" Then
findvalue = "*" & findvalue & "*"
End If
If UBound(cols) = -1 Then
ReDim chiso(1 To UBound(data, 2) - LBound(data, 2) + 1)
For c = 1 To UBound(chiso)
chiso(c) = c
Next c
Else
chiso = cols
End If
For r = LBound(data) To UBound(data)
For Each c In chiso
If ignorecase Then
value_ = LCase(data(r, LBound(data, 2) + c - 1))
Else
value_ = data(r, LBound(data, 2) + c - 1)
End If
If value_ Like findvalue Then
count = count + 1
ReDim Preserve result(LBound(data, 2) To UBound(data, 2), 1 To count)
For k = LBound(data, 2) To UBound(data, 2)
result(k, count) = data(r, k)
Next k
Exit For
End If
Next c
Next r
If count Then ListBox.Column = result
end_:
End Sub
