如何在Excel工作簿的所有工作表中搜索字符串? [英] How to search for a string in all sheets of an Excel workbook?
问题描述
我写了一个宏,它将在Excel工作簿的所有工作表中搜索一个字符串.该宏将激活第一张工作表以及该工作表中包含搜索字符串的单元格.如果找不到,它将显示一条消息.
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.
我想扩展此功能,以涵盖包含此字符串的所有工作表,而不仅仅是第一个.因此,我修改了宏,但是它没有按预期工作.我给出了下面的代码,并在显示错误的地方进行了注释.
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
推荐答案
我能够解决我的问题,并为可能需要它的人发布了代码
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
这篇关于如何在Excel工作簿的所有工作表中搜索字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!