7

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
TylerH
  • 20,799
  • 66
  • 75
  • 101
samar
  • 5,021
  • 9
  • 47
  • 71
  • Sorry, but your code has a few inconsistencies and I can't figure out exactly what you are trying to do. Please describe what your program is supposed to do when there are multiple matches in a Sheet. And BTW, take a look at the FindNext method – Dr. belisarius Jan 19 '11 at 13:48
  • My code does the following: 1. searches for first available match in all sheets. If found then it activates the sheet and the cell in which the search string is present. 2. searches for next available match. If present then shows a msgbox with yes/no saying that there are more searches available. Do you want to continue? If yes then the next available match is shown as mentioned in point 1 and then searches for the next available match and so on till all the matches are exhausted. 3. If no matches is available then the process stops. Also whose method is this FindNext, "Cells" or "Sheets"? – samar Jan 20 '11 at 06:48
  • Sorry am I missing something... what's wrong with Control + F? (find?) The default searches only the active sheet, but in the Options on the Find dialog you can change the search scope to workbook. I'm sure there would be a way to programmatically call the built in function and save yourself unnecessary work. – RichardW1001 Jan 20 '11 at 20:01
  • @Richard, I had created the method using this functionality only i.e. recording the macro and making the changes as required. But i want to extend this functionality for continued search which is creating a problem as mentioned above. – samar Jan 21 '11 at 08:57
  • Possible duplicate of [How can I run VBA in multiple Wksheets simultaneously?](https://stackoverflow.com/questions/52386104/how-can-i-run-vba-in-multiple-wksheets-simultaneously) – TylerH Sep 18 '18 at 13:54

3 Answers3

7

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
TylerH
  • 20,799
  • 66
  • 75
  • 101
samar
  • 5,021
  • 9
  • 47
  • 71
  • 1
    Please describe what you changed so that future readers can benefit. Walls of code are seldom helpful. – TylerH Sep 18 '18 at 13:52
1

Here's a much simpler solution that may work fine for most people:

This is a macro that will quickly bring up the "Find" dialog with the proper settings to search across all worksheets within a workbook.

Sub FindInWorkbook()
    SendKeys "^f%t%hw{ENTER}%n"
End Sub
James
  • 11
  • 1
0

The problem is that Cells.Find returns a range. When you use it in your function HasMoreValues, you use it like this:

Cells.Find(...).Value

But the returned range does not convert to a .value correctly. You can fix this by using .text instead of .value, like this:

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 set the 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.Find always returns a range of one cell, so you might be okay.

Steven
  • 920
  • 2
  • 9
  • 21
  • i tried your way of using .Text instead of .Value but it is giving the same issue. Anyways I have just found a workaround to the problem and i will be posting the same here now. Thanks for your help. – samar Jan 25 '11 at 06:20
  • 1
    @samar Have you tried setting a `Range` variable to the result of the Find, and then using Range.Cells(1,1).Text to get at the text? – Steven Jan 25 '11 at 15:05