CODEWITHSUNDEEP
vbaexcelexcel-2010
Cris Reis
Cris Reis

Reputation: 179

Search for multiple strings in multiple workbooks at the same time

How can I search for several strings automatically? The number of strings is variable, and are in column A, Sheet “Plan1”, workbook “"Book1.xlsm". I used Find Method for search and a Input Box to find the string, one by one, in a looping for multiple worbooks. I would like to substitute this Input Box to a loop through the strings. Part of my code:

    Dim wb As Workbook
Dim SearchString As String
Dim SearchRange As Range, cl As Range
Dim Escolhe_Cor As String
Dim FirstFound As String
Dim ws As Worksheet

str = InputBox("Digite o número a ser procurado")
Escolhe_Cor = InputBox("Escolha uma cor para destacar esse número. De 3 a 56")
Application.FindFormat.Clear

  SearchString = Trim(str)


 For Each wb In Workbooks
   If wb.Name <> "Book1.xlsm" Then
    wb.Activate

 If Len(SearchString) = "8" Then

    For Each ws In ActiveWorkbook.Worksheets
             ' Find first instance on sheet
    Set cl = ws.Cells.Find(What:=SearchString, _
        After:=ws.Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)
             If Not cl Is Nothing Then
               ' if found, remember location
                 FirstFound = cl.Address
                 ' format found cell

            Do 'etc etc

Upvotes: 0

Views: 999

Answers (1)

user2063626
user2063626

Reputation:

Try below code :

 Dim wb As Workbook
    Dim SearchString As String
    Dim SearchRange As Range, cl As Range
    Dim Escolhe_Cor As String
    Dim FirstFound As String
    Dim ws As Worksheet
    Dim searchRng As Range, lastRow As Long, cell As Range


    Dim lastRow As Long
    lastRow = Workbooks("Book1.xlsm").Sheets("Plan1").Range("65000").End(xlUp).Row

    Set searchRng = Workbooks("Book1.xlsm").Sheets("Plan1").Range("A2:A" & lastRow)   '

    For Each cell In searchRng
           SearchString = Trim(cell)

        For Each wb In Workbooks
            If wb.Name <> "Book1.xlsm" Then
                wb.Activate

                If Len(SearchString) = "8" Then

                    For Each ws In ActiveWorkbook.Worksheets
                        ' Find first instance on sheet
                        Set cl = ws.Cells.Find(What:=SearchString, _
                                               After:=ws.Cells(1, 1), _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False, _
                                               SearchFormat:=False)
                        If Not cl Is Nothing Then
                            ' if found, remember location
                            FirstFound = cl.Address

Upvotes: 2

Related Questions