使用宏搜索excel文件的所有表格中的字符串 [英] search for string in all sheets of an excel file using macros

问题描述

我已经编写了一个宏，它会在excel文件的所有表中搜索一个字符串。此宏将激活第一个工作表以及包含搜索字符串的工作表中的单元格。如果没有找到，那么它将显示一条消息。这个宏运行正常。我想扩展这个功能来覆盖包含这个字符串而不是第一个的所有表。所以我修改了宏，但没有按预期工作。我已经给出了下面的代码，并在出现错误的地方发表了评论。

 
 Dim sheetCount As Integer 
 Dim datatoFind 
 
子按钮1_Click（）
 
 Find_Data 
 
结束子
 
私有子查找数据（）
 Dim计数器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（请输入要搜索的值）
如果datatoFind =然后退出Sub 
 sheetCount = ActiveWorkbook.Sheets.Count 
如果IsError（CDbl（datatoFind））= False然后datatoFind = CDbl（datatoFind）
对于计数器= 1到sheetCount 
表（计数器）.Activate 
 
单元格.Find（What：= datatoFind，After：= ActiveCell，LookIn：= xlFormulas，LookAt _ 
：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，Ma tchCase：= _ 
 False，SearchFormat：= False）。激活
 
如果InStr（1，ActiveCell.Value，datatoFind）然后
如果HasMoreValues（counter + 1）Then'没有完成方法并直接输入
 yesNo = MsgBox（你想继续搜索吗，vbYesNo）
如果yesNo = vbNo然后
 notFound = False 
退出
结束如果
结束如果
表（计数器）.Activate 
结束如果
下一个计数器
如果notFound然后
 MsgBox（找不到值）
表（currentSheet）.Activate 
结束如果
 End Sub 
 
私有函数HasMoreValues（ByVal sheetCounter As Integer）As Boolean 
 HasMoreValues = False 
 Dim str As String 
 
对于counter = sheetCounter到sheetCount 
表（counter）.Activate 
 
 str = Cells.Find（what：= datatoFind ，之后：= ActiveCell，LookIn：= xlFormulas，LookAt _ 
：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，MatchCase：= _ 
 False，SearchFormat：= False）.Value'不会进一步即不执行以下代码
 
如果InStr（1，str，datatoFind）然后
 HasMoreValues = True 
退出
结束如果
下一个计数器
结束功能
 
 
 

解决方案

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

 
 
 Dim sheetCount As Integer 
 Dim datatoFind 
 
子按钮1_Click（）
 
 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.Inde x 
 datatoFind = StrConv（InputBox（请输入要搜索的值），vbLowerCase）
如果datatoFind =然后退出Sub 
 sheetCount = ActiveWorkbook.Sheets.Count 
如果IsError（CDbl（datatoFind））= False然后datatoFind = CDbl（datatoFind）
对于计数器= 1到sheetCount 
表（计数器）.Activate 
 
 Cells.Find什么：= datatoFind，之后：= ActiveCell，LookIn：= xlFormulas，LookAt _ 
：= xlPart，SearchOrder：= xlByRows，SearchDirection：= xlNext，MatchCase：= _ 
 False，SearchFormat：= False） .Activate 
 
如果InStr（1，StrConv（ActiveCell.Value，vbLowerCase），datatoFind）然后
 notFound = False 
如果HasMoreValues（counter）Then 
 yesNo = MsgBox（你想继续搜索吗，vbYesNo）
如果yesNo = vbNo然后
表（计数器）.Activate 
退出
结束如果
 Els e 
表（计数器）。激活
退出
结束如果
表（计数器）.Activate 
结束如果
下一个计数器
如果notFound Then 
 MsgBox（Value not found）
表（currentSheet）.Activate 
如果
 End Sub 
 
私有函数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 
 
对于counter = sheetCounter + 1到sheetCount 
表（计数器）.Activate 
 
 lastRow = ActiveCell.SpecialCells（xlLastCell）.Row 
 lastCol = ActiveCell.SpecialCells xlLastCell）.Column 
 
对于vRow = 1 to lastRow 
对于vCol = 1 to lastCol 
 str = Sheets（counter）.Cells（vRow，vCol）.Text 
如果InStr（1，StrConv（str，vbLowerCase）， datatoFind）然后
 HasMoreValues = True 
退出
结束如果
下一个vCol 
 
如果HasMoreValues然后
退出
结束如果
下一个vRow 
 
如果HasMoreValues然后
表（sheetCounter）。激活
退出
结束如果
下一个计数器
结束功能
 
 

问候，

Samar

I have written a macro which will search a string in all the sheets of an excel file. 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. This macro runs fine. I wanted to extend this functionality to cover all the sheets which contains this string and not 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

Regards,

Samar

这篇关于使用宏搜索excel文件的所有表格中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！