Excel VBA通配符搜索 [英] Excel VBA wildcard search
问题描述
John Abe Smith
Cindy巨魔蜜蜂
Randy Row
Joe Jumbo
Katie Kool Kat
我想写一个宏当我在文本框中输入内容并单击按钮时,它将在该列中查找该名称,并且在有一个msgbox后,只需要说找到,然后 EXIT SUB
但在搜索名称时,我想使用通配符*,但我不知道如何。我现在有一些这样的代码,但通配符不起作用:
Private Sub search_Click()
For firstloop = 3到10
如果Range(G& firstloop).Text = name.Text& *然后
MsgBoxFound!
退出Sub
Else
MsgBoxNOT FOUND
End If
Next
End Sub
让我们在文本框中输入Troll,然后点击按钮,我想让循环遍历列,找到Troll在里面。我如何去做这个?
你可以使用=nofollow> 像
操作符(区分大小写):
Private Sub search_Click()
对于firstloop = 3到10
如果Range(G& firstloop).Text Like name.Text& *然后
MsgBoxFound!
退出Sub
Else
MsgBoxNOT FOUND
End If
Next
End Sub
for case-insensitive 搜索使用:
如果UCase(Range(G& firstloop).Text)像UCase(name.Text)& *然后
另外,如果要确定单元格是否包含文本(不仅开始与文本),您可以使用(区分大小写):
code> If InStr(1,Range(G& firstloop).Text,name.Text)> 0然后
或(不区分大小写)
如果InStr(1,Range(G& firstloop).Text,name.Text,vbTextCompare)> 0然后
UPD:
如果点只显示 msgbox
,那么我建议使用 Application.Match
:
Private Sub search_Click()
如果不是IsError(Application.Match(abc&*范围(G3:G10),0))然后
MsgBoxFound!
Else
MsgBoxNOT FOUND
End If
End Sub
i currently have an excel file with 1 column and many rows, the column holds first name and last name and possibly a middle name (EXAMPLE: John Abe Smith). i am writing a macro that has 1 textbox and 1 button. let's say in the excel sheet i have a couple names:
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 and after have a msgbox just say "found" then EXIT SUB
but while searching for the names, i want to use the wildcard "*" 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
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. how could i go about doing this?
You can use Like
operator (case-sensitive):
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-insensitive search use:
If UCase(Range("G" & firstloop).Text) Like UCase(name.Text) & "*" Then
Also if you want to determine whether cell contains text (not only starts with 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
:
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
这篇关于Excel VBA通配符搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!