vba 如何在 Excel 工作簿的所有工作表中搜索字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4734794/
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
How to search for a string in all sheets of an Excel workbook?
提问by samar
I have written a macro which will search for a string in all the sheets of an Excel workbook. This macro will activate the first sheet as well as the cell in the sheet which contains the search string. If not found, then it will show a message.
我编写了一个宏，它将在 Excel 工作簿的所有工作表中搜索一个字符串。此宏将激活第一个工作表以及工作表中包含搜索字符串的单元格。如果未找到，则会显示一条消息。
I want to extend this functionality to cover all the sheets which contain this string and not just the first one. So I modified the macro, but it is not working as expected. I have given the code below and also commented at the place where it is showing the error.
我想扩展此功能以涵盖包含此字符串的所有工作表，而不仅仅是第一个工作表。所以我修改了宏，但它没有按预期工作。我已经给出了下面的代码，并在它显示错误的地方进行了评论。
Dim sheetCount As Integer
Dim datatoFind
Sub Button1_Click()
Find_Data
End Sub
Private Sub Find_Data()
Dim counter As Integer
Dim currentSheet As Integer
Dim notFound As Boolean
Dim yesNo As String
notFound = True
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If InStr(1, ActiveCell.Value, datatoFind) Then
If HasMoreValues(counter + 1) Then 'Not completing the method and directly entering
yesNo = MsgBox("Do you want to continue search?", vbYesNo)
If yesNo = vbNo Then
notFound = False
Exit For
End If
End If
Sheets(counter).Activate
End If
Next counter
If notFound Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub
Private Function HasMoreValues(ByVal sheetCounter As Integer) As Boolean
HasMoreValues = False
Dim str As String
For counter = sheetCounter To sheetCount
Sheets(counter).Activate
str = Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Value 'Not going further than this i.e. following code is not executed
If InStr(1, str, datatoFind) Then
HasMoreValues = True
Exit For
End If
Next counter
End Function
采纳答案by samar
I was able to solve my problem and have posted the code for the ones who might need it
我能够解决我的问题并为可能需要它的人发布了代码
Dim sheetCount As Integer
Dim datatoFind
Sub Button1_Click()
Find_Data
End Sub
Private Sub Find_Data()
Dim counter As Integer
Dim currentSheet As Integer
Dim notFound As Boolean
Dim yesNo As String
notFound = True
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = StrConv(InputBox("Please enter the value to search for"), vbLowerCase)
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If InStr(1, StrConv(ActiveCell.Value, vbLowerCase), datatoFind) Then
notFound = False
If HasMoreValues(counter) Then
yesNo = MsgBox("Do you want to continue search?", vbYesNo)
If yesNo = vbNo Then
Sheets(counter).Activate
Exit For
End If
Else
Sheets(counter).Activate
Exit For
End If
Sheets(counter).Activate
End If
Next counter
If notFound Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub
Private Function HasMoreValues(ByVal sheetCounter As Integer) As Boolean
HasMoreValues = False
Dim str As String
Dim lastRow As Long
Dim lastCol As Long
Dim rRng As Excel.Range
For counter = sheetCounter + 1 To sheetCount
Sheets(counter).Activate
lastRow = ActiveCell.SpecialCells(xlLastCell).Row
lastCol = ActiveCell.SpecialCells(xlLastCell).Column
For vRow = 1 To lastRow
For vCol = 1 To lastCol
str = Sheets(counter).Cells(vRow, vCol).Text
If InStr(1, StrConv(str, vbLowerCase), datatoFind) Then
HasMoreValues = True
Exit For
End If
Next vCol
If HasMoreValues Then
Exit For
End If
Next vRow
If HasMoreValues Then
Sheets(sheetCounter).Activate
Exit For
End If
Next counter
End Function
回答by Steven
The problem is that Cells.Findreturns a range. When you use it in your function HasMoreValues, you use it like this:
问题是Cells.Find返回一个范围。当你在你的函数中使用它时HasMoreValues，你可以这样使用它：
Cells.Find(...).Value
But the returned range does not convert to a .value correctly. You can fix this by using .textinstead of .value, like this:
但是返回的范围不会正确转换为 .value。您可以使用.text代替来解决此问题.value，如下所示：
Cells.Find(...).text
Or completely:
或者完全：
str = Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).text
To be completely correct, you should probably setthe result of the find to a Range variable, and then access it through that, in case the Find search returns nothing. However according to the documentation Cells.Findalways returns a range of one cell, so you might be okay.
为了完全正确，您可能应该set将查找结果放到 Range 变量中，然后通过该变量访问它，以防 Find 搜索返回任何内容。但是根据文档Cells.Find总是返回一个单元格的范围，所以你可能没问题。
