|
不会用正则,用数组循环查找实现多个关键词的查找。供参考。
Sub myfind()
With Sheets("数据库")
lastrow = .Range("c" & Rows.Count).End(xlUp).Row
mydata = .Range("c3:c" & lastrow)
inputkey = CStr(InputBox("请输入要查找的关键词,多个关键词以英文逗号隔开", "Enter Data"))
If inputkey = "" Then Exit Sub
mykeys = Split(inputkey, ",")
Set d_key = CreateObject("scripting.dictionary")
For i = LBound(mykeys) To UBound(mykeys)
If mykeys(i) <> "" Then d_key(mykeys(i)) = ""
Next
count_key = d_key.Count
mykey = d_key.keys
Set d_key = Nothing
Set d_data = CreateObject("scripting.dictionary")
For i = LBound(mydata) To UBound(mydata)
If mydata(i, 1) <> "" Then d_data(mydata(i, 1)) = ""
Next
count_data = d_data.Count
mydatas = d_data.keys
Set d_data = Nothing
ReDim arr(1 To count_data)
For j = 1 To count_data
p = 1
For i = 1 To count_key
If InStr(mydatas(j - 1), mykey(i - 1)) = 0 Then
p = 0
Exit For
End If
Next
If p = 1 Then
t = t + 1
arr(t) = mydatas(j - 1)
End If
Next
If t = 0 Then Exit Sub
Set d_result = CreateObject("scripting.dictionary")
For i = 1 To t
d_result(arr(i)) = ""
Next
count_result = d_result.Count
arr_result = Application.Transpose(d_result.keys)
Set d_result = Nothing
.Range("f3").Resize(lastrow - 2, 1).ClearContents
.Range("f3").Resize(count_result, 1) = arr_result
End With
End Sub |
|