Excel VBA 通配符搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23087446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA wildcard search
提问by HumanlyRespectable
I currently have an Excel file with 1 column and many rows. The column holds a first name, last name, and possibly a middle name (EXAMPLE: John Abe Smith). I am writing a macro that has 1 textbox and 1 button. In the excel sheet I have a couple names:
我目前有一个包含 1 列和多行的 Excel 文件。该列包含一个名字、姓氏，可能还有一个中间名（示例：John Abe Smith）。我正在编写一个具有 1 个文本框和 1 个按钮的宏。在excel表中，我有几个名字：
Column A
--------
John Abe Smith
Cindy Troll Bee
Randy Row
Joe Jumbo
Katie Kool Kat
I want to write a macro that when I type something in the textbox and click the button, it will look in this column for the name. If it finds it, then just say "found" in a message box.
我想编写一个宏，当我在文本框中键入内容并单击按钮时，它将在此列中查找名称。如果找到了，那么只需在消息框中说“找到”即可。
I want to use the wildcard "*" when searching the names, but I do not know how. I currently have some code like this, but the wildcard does not work:
我想在搜索名称时使用通配符“*”，但我不知道如何使用。我目前有一些这样的代码，但通配符不起作用：
Private Sub search_Click()
For firstloop = 3 To 10
If Range("G" & firstloop).Text = name.Text & "*" Then
MsgBox "Found!"
Exit Sub
Else
MsgBox "NOT FOUND"
End If
Next
End Sub
For example, let's say I type in "Troll" in the text box and I click the button. I want the loop to go through the column to find anything with "Troll" in it. The result from the example data would be just Cindy Troll Bee.
例如，假设我在文本框中输入“ Troll”并单击按钮。我希望循环遍历该列以查找其中包含“Troll”的任何内容。示例数据的结果将只是Cindy Troll Bee.
How could I go about doing this?
我怎么能去做这件事？
回答by Dmitry Pavliv
You can use Likeoperator (case-sensitive):
您可以使用Like运算符（区分大小写）：
Private Sub search_Click()
For firstloop = 3 To 10
If Range("G" & firstloop).Text Like name.Text & "*" Then
MsgBox "Found!"
Exit Sub
Else
MsgBox "NOT FOUND"
End If
Next
End Sub
for case-insensitivesearch use:
对于不区分大小写的搜索使用：
If UCase(Range("G" & firstloop).Text) Like UCase(name.Text) & "*" Then
Also if you want to determine whether cell containstext (not only startswith text), you can use (case-sensitive):
另外，如果要确定单元格是否包含文本（不仅以文本开头），还可以使用（区分大小写）：
If InStr(1, Range("G" & firstloop).Text, name.Text) > 0 Then
or (case-insensitive)
或（不区分大小写）
If InStr(1, Range("G" & firstloop).Text, name.Text, vbTextCompare) > 0 Then
UPD:
更新：
If the point only to show msgbox, then I'd suggest to use Application.Match:
如果重点只是为了显示msgbox，那么我建议使用Application.Match：
Private Sub search_Click()
If Not IsError(Application.Match("abc" & "*", Range("G3:G10"), 0)) Then
MsgBox "Found!"
Else
MsgBox "NOT FOUND"
End If
End Sub
回答by Thierry Dalon
You can also avoid a loop and use the Range.FindMethod. You can pass as option if you want it case sensitive or not. (By default it is not case sensitive.)
您还可以避免循环并使用Range.Find方法。如果您希望区分大小写，您可以作为选项传递。（默认情况下不区分大小写。）
Set rngFound= Range("G" & firstloop).Find(name.Text & "*")
If rngFound Is Nothing Then
MsgBox "Not Found!"
Else
MsgBox "FOUND"
End If
