Search for a ‘string’ in Excel Workbook

The following code can be used to search a specific string in an Excel workbook within all the sheets. Copy the code and paste it in a module. if you have any questions, let me know.

Code:
Sub CommandButton1_Click()         Dim ThisAddress$, Found, FirstAddress

    Dim  Lost$, N&, NextSheet&

    Dim CurrentArea As  Range, SelectedRegion As Range

    Dim Reply As VbMsgBoxResult

    Dim FirstSheet As  Worksheet

    Dim Ws As Worksheet

    Dim Wks As Worksheet

    Dim Sht As Worksheet

     

    Set FirstSheet = ActiveSheet ‘< bookmark start sheet

    Lost =  InputBox(prompt:=”Type in the details you are looking for!”, _

    Title:=”  Find what?”, Default:=”*”)

    If Lost = Empty Then End

    For Each Ws In Worksheets

        Ws.Select

        With ActiveSheet.Cells

            Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)

            If FirstAddress Is Nothing Then ‘< blank sheet

                Goto NextSheet

            End If

            FirstAddress.Select

             ‘    Selection.Interior.ColorIndex = 6 ‘< yellow

             ‘//colour the ‘Lost’ font red, cell colour blank

            With Selection

                Set Found = .Find(What:=Lost, LookIn:=xlValues)

                If Not Found Is Nothing Then

                    FirstAddress = Found.Address

                    Do

                         ‘     Found.Interior.ColorIndex = 3 ‘< red

                         ‘    Found.Font.Bold = True

                         ‘   Found.Font.ColorIndex = 2

                         ‘   Set Found = .FindNext(Found)

                     Loop While Not Found Is Nothing And Found. _

                    Address <> FirstAddress

                End If

            End With

            Reply =  MsgBox(“Is this the ” & Lost & ” you’re looking for?”, _

            vbQuestion + vbYesNoCancel, “Current   Region”)

             ‘//restore the ‘Lost’ font and cell colour

            Set Found = .Find(What:=Lost, LookIn:=xlValues)

            If Not Found Is Nothing Then

                FirstAddress = Found.Address

                Do

                     ‘ Found.Font.Bold = False

                     ‘Found.Font.ColorIndex = 0

                    Set Found = .FindNext(Found)

                Loop While Not Found Is Nothing And Found. _

                Address <> FirstAddress

            End If

             ‘//restore the selection colour

             ‘  Selection.Interior.ColorIndex = xlNone

            Set FirstAddress = .Find(What:=Lost, LookIn:=xlValues)

            If Reply = vbCancel Then End

             ‘//dont look further

            If Reply = vbYes Then

                Set SelectedRegion = Selection

Goto Finish:

            End If

             ‘//    case=not this one

            ThisAddress = FirstAddress.Address

            Set CurrentArea = Selection

            Do

                If  Intersect(CurrentArea, Selection) Is Nothing Then

                     ”  With Selection.Interior

                     ‘    .ColorIndex = 6

                     ‘   .Pattern = xlSolid

                     ‘   End With

                     ‘//colour the ‘Lost’ font red, cell colour blank

                    With Selection

                        Set Found = .Find(What:=Lost, LookIn:=xlValues)

                        If Not Found Is Nothing Then

                            FirstAddress = Found.Address

                            Do

                                 ‘  Found.Interior.ColorIndex = 3

                                 ‘ Found.Font.Bold = True

                                 ‘Found.Font.ColorIndex = 2

                                Set Found = .FindNext(Found)

                            Loop While Not Found Is Nothing And Found. _

                            Address <> FirstAddress

                        End If

                    End With

                    Reply = MsgBox(“Is this the ” & Lost & ” you’re looking for?”, _

                    vbQuestion + vbYesNoCancel, “Current Region”)

                     ‘//restore the ‘Lost’ font and cell colour

                    Set Found = .Find(What:=Lost, LookIn:=xlValues)

                    If Not Found Is Nothing Then

                        FirstAddress = Found.Address

                        Do

                             ‘  Found.Font.Bold = False

                             ‘ Found.Font.ColorIndex = 0

                            Set Found = .FindNext(Found)

                        Loop While Not Found Is Nothing And Found. _

                        Address <> FirstAddress

                    End If

                     ‘//restore the selection colour

                     ‘  Selection.Interior.ColorIndex = xlNone

                    Set FirstAddress = .Find(What:=Lost, _

                    LookIn:=xlValues)

                    If Reply = vbCancel Then End

                    If Reply = vbYes Then

                        Set SelectedRegion = Selection

Goto Finish:

                    End If

                End If

                If CurrentArea Is Nothing Then

                    Set CurrentArea = Selection

                Else

                    Set CurrentArea = Union(CurrentArea, Selection)

                End If

                Set FirstAddress = .FindNext(FirstAddress)

                FirstAddress.Select

            Loop While Not FirstAddress Is Nothing And FirstAddress. _

            Address <> ThisAddress

        End With

NextSheet:

    Next Ws

Finish:

    If Reply = vbYes Then

        Exit Sub

    Else

        FirstSheet.Select

        MsgBox “Search Completed – Sorry, no more ” & Lost & “s”, _

        vbInformation, “No Region Selected”

    End If

End Sub

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: