gpt4 book ai didi

vba - 如何在Excel工作簿的所有工作表中搜索字符串?

转载 作者:行者123 更新时间:2023-12-01 22:33:19 25 4
gpt4 key购买 nike

我编写了一个宏,它将在 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

最佳答案

我能够解决我的问题,并为可能需要它的人发布了代码

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

关于vba - 如何在Excel工作簿的所有工作表中搜索字符串?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4734794/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com