[RESOLVED] Find all occurrence of a string in E:E from multiple worksheets.

[m.davide]

I have Worsheets(1) is where the searching occurs. Column E of Worksheets(2 to n) will be searched.
In worksheet(1) I have a textbox the receives the string to be searched.

countWS = ThisWorkbook.Sheets.Count
resultRow = 5
For cursor = 2 To countWS
   'seach and paste each result on Worksheets(1).Cells(resultRow, 2)
   'resultRow = resultRow +1
Next cursor

[westconn1]

 for ws = 2 to thisworkbook.sheets.count
    set fnd = thisworkbook.sheets(ws).cells(rows.count, 5).end(xlup)
    do    
       set fnd = thisworkbook.sheets(ws).range("e:e").find(sheet1.textbox1.value, fnd)
       if fnd is nothing then exit do
       thisworkbook.sheets(1).cells(resultrow, 2).value = fnd & " at " & thiswokbook.sheets(ws).name & " " & fnd.address
    loop
next

this code must be written in a procedure in sheets(1) module, assumes sheets(1) is sheet1 (whichever sheet has the textbox)

i did not test this, so there could be some error

[m.davide]

 for ws = 2 to thisworkbook.sheets.count
    set fnd = thisworkbook.sheets(ws).cells(rows.count, 5).end(xlup)
    do    
       set fnd = thisworkbook.sheets(ws).range("e:e").find(sheet1.textbox1.value, fnd)
       if fnd is nothing then exit do
       thisworkbook.sheets(1).cells(resultrow, 2).value = fnd & " at " & thiswokbook.sheets(ws).name & " " & fnd.address
    loop
next

this code must be written in a procedure in sheets(1) module, assumes sheets(1) is sheet1 (whichever sheet has the textbox)

i did not test this, so there could be some error

Hi, I test-searched a string that supposed to just return 4 results. When I ran the code it looped forever. Instead of displaying the results I change it to count the results just so I can show you. I used Ctrl-Break to stop. Also it doesn't move to the next sheet. It just loops the second sheet.

Private Sub SearchBox_GotFocus()
    SearchBox.BackColor = vbWhite
End Sub

Private Sub SearchButton_Click()
    If SearchBox.Text = "" Then
        SearchBox.BackColor = vbRed
        MsgBox "Search parameter is empty. Please fill-in the highlighted box.", vbCritical
    Else
        Find_String
    End If
End Sub

Private Sub Find_String()
    Dim searchString As String
    Dim countWS, counter As Integer
    Dim ws As Integer
    Dim resultRow As Integer
    
    searchString = SearchBox.Value
    countWS = ThisWorkbook.Sheets.Count
    resultRow = 5
    counter = 0

    For ws = 2 To countWS
        Set fnd = ThisWorkbook.Sheets(ws).Cells(Rows.Count, 5).End(xlUp)
        Do
            Set fnd = ThisWorkbook.Sheets(ws).Range("E:E").Find(searchString, fnd)
            If fnd Is Nothing Then
                ThisWorkbook.Sheets(1).Cells(resultRow, 2).Value = "NO RESULTS FOUND"
                Exit Do
            Else
                'ThisWorkbook.Sheets(1).Cells(resultRow, 2).Value = fnd
                'ThisWorkbook.Sheets(1).Cells(resultRow, 3).Value = Sheets(ws).Name
                'ThisWorkbook.Sheets(1).Cells(resultRow, 4).Value = fnd.Address
                'resultRow = resultRow + 1
                ThisWorkbook.Sheets(1).Cells(1, 2).Value = counter
                counter = counter + 1
            End If
        Loop
    Next ws
End Sub

[westconn1]

i have tested this code, it will exit from the do loop correctly replace the original content of my for loop with

Set firstfnd = s.Range("a1")
Set fnd = s.Cells(Rows.Count, 5).End(xlUp).Offset(1)
Do
Set fnd = s.Range("e:e").Find("gold", fnd)
If fnd Is Nothing Then Exit Do
If fnd.Address = firstfnd.Address Then Exit Do

Debug.Print fnd.parent.name, fnd.Address
If firstfnd.Address = "$A$1" Then Set firstfnd = fnd
cnt = cnt + 1
Loop
Set firstfnd = s.Range("a1")

replace sheet object s with thisworkbook.sheets(ws) in all instances, i did not test the for loop, but doubt that it will give any serious problem

[m.davide]

thank you!