我编写了一个宏,它将在Excel工作簿的所有工作表中搜索一个字符串 . 此宏将激活第一个工作表以及包含搜索字符串的工作表中的单元格 . 如果没有找到,那么它将显示一条消息 .
我想扩展此功能以涵盖包含此字符串的所有工作表,而不仅仅是第一个工作表 . 所以我修改了宏,但它没有按预期工作 . 我已经给出了下面的代码,并在显示错误的地方发表了评论 .
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
2 回答
我能够解决我的问题,并为可能需要它的人发布了代码
问题是
Cells.Find
返回一个范围 . 当您在函数HasMoreValues
中使用它时,您可以像这样使用它:但返回的范围不能正确转换为.value . 您可以使用
.text
而不是.value
来解决此问题,如下所示:或完全:
为了完全正确,你应该__24546_找到一个Range变量的结果,然后通过它访问它,以防查找搜索没有返回任何内容 . 但是根据文档
Cells.Find
总是返回一个单元格的范围,所以你可能没问题 .