Excel VBA 搜索按钮
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16422769/
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 Search Button
提问by user2358930
I am trying to use a text box and command button to search my entire workbook for a specific word or value. For example, "3132" or "Work Instructions". So far I am able to search the sheet that I am on but I cannot search the rest of the workbook. Plus, some of the worksheets are hidden. Any insight to this would be beneficial and help me out a ton! I have listed my currect program below:
我正在尝试使用文本框和命令按钮在整个工作簿中搜索特定单词或值。例如，“3132”或“工作说明”。到目前为止，我可以搜索我所在的工作表，但我无法搜索工作簿的其余部分。另外，一些工作表是隐藏的。对此的任何见解都将是有益的，并能帮我很多忙！我在下面列出了我当前的程序：
Private Sub CommandButton6_Click()
Dim strFindWhat As String
strFindWhat = TextBox1.Text
On Error GoTo ErrorMessage
Cells.Find(What:=strFindWhat, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Select
Exit Sub
ErrorMessage:
MsgBox ("The data you are searching for does not exist")
End Sub
Have a good one!
祝你有个好的一天！
回答by Declan_K
You need to loop through the array of worksheet objects in the workbook.worksheetscollection.
您需要遍历workbook.worksheets集合中的工作表对象数组。
回答by David Zemens
Try something like this, which uses FindNextmethod while looping over the Sheets in the Workbook.
尝试这样的事情，它FindNext在循环工作簿中的工作表时使用方法。
Sub FindLoopSheets()
Dim srchString$
Dim w As Integer
Dim wsName As String
Dim rng As Range
Dim fndRange As Range
Dim nxtRange As Range
srchString = Application.InputBox("Enter the value to search for", "Search Query")
If srchString = vbNullString Then
MsgBox "No value entered.", vbInformation
Exit Sub
End If
For w = 1 To Sheets.Count
With Sheets(w)
wsName = .Name
Debug.Print "Beginning search on " & wsName
Set rng = .Cells.Find(What:=srchString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not rng Is Nothing Then
Set fndRange = rng
Do
Set nxtRange = .Cells.FindNext(After:=fndRange)
Debug.Print Sheets(w).Name & "!" & nxtRange.Address
Set fndRange = nxtRange
Loop Until fndRange.Address = rng.Address
Else:
Debug.Print srchString & " was not found on " & wsName
End If
End With
Next w
End Sub
