Search box for whole workbook

[DomiDarko]

Greetings to the Excel community.

I am trying to develop my workbook design for my employers. I am advanced with formulas but have just started VBA last week and have a book arriving tmrw to help me.
So far i have already added a search function using a command button and text box to each worksheet and a toggle button to find and hide/show rows users dont need automatically with great success
I am currently adapting code from the internet and only writing small bits here and there as my knowledge expands.

Here is the code i have adapted so far. Its not working correctly and i cant seem to figure it out. The command button and textbox will sit on the first worksheet and needs to search for a name (example: John) in the workbook and either display which sheet the name is found on or jump in turn to each name until you reach the correct sheet.
Do i need to set a range for the variable (ws)?
Many Thanks for any input.

Private Sub CommandButton1_Click()

Dim search As String
Dim ws As Worksheet
Dim Answer As String
search = TextBox1.Text
On Error GoTo ErrorMessage

Application.EnableEvents = True

For Each ws In ActiveWorkbook.Worksheets
Cells.Find(What:=search, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select

If ActiveCell.Row Then
Answer = MsgBox("Found at " & ActiveSheet.Name & " Continue searching ? ", vbYesNo)
If Answer = vbNo Then Exit Sub

ErrorMessage:
MsgBox ("Not Found")

End If
Next ws
End Sub

 

[DomiDarko]

Sub find()


    Dim ws As Worksheet
    Dim search As String
    search = "karel"
    
    For Each ws In ThisWorkbook.Worksheets
        Dim r As Range
        If ws.Name = ThisWorkbook.Sheets(1).Name Then GoTo nws
        Set r = ws.Cells.find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not r Is Nothing Then
            Application.Goto (Sheets(ws.Name).Range(r.Address))
            MsgBox "found on " & ws.Name
        Else
            MsgBox "not found on " & ws.Name
        End If
nws:
    Next ws
End Sub

for Q2: search will serach all occurences on worksheet. if you do not want this, place "goto nws" after the "found on..." msgbox

Thanks again Storm8. Excellent stuff!
I'll try this on Monday morning.

I'm learning a lot from your posts so its much appreciated.
My book has arrived am i am playing about with msg box's and objects, functions and properties.
I think i finally understand how and where to write this sort of stuff to a certain degree.
I am probably going to add to it as i progress, maybe with an input box or something.

 

[DomiDarko]

Good morning! So i tried your code out this morning but the 'nws' bit was creating an error so i adapted it a bit and it is working almost perfectly apart from one thing.
It does everything i want apart from, when it finds the string on a page where there is more than one, it only finds the first one then it skips onto the next worksheet - ingoring the other strings on the worksheet.
I am trying to adjust the code so it continues to search the same page when the continue button is pressed but its just beyond my knowledge at the moment.
Sorry to keep asking but im so close!!!

'SEARCHBOX FOR WHOLE WORKBOOK

Private Sub CommandButton1_Click()
Dim search As String
Dim ws As Worksheet
Dim Answer As String
Dim r As Range
search = TextBox1.Text

Application.EnableEvents = True

For Each ws In ThisWorkbook.Worksheets
Set r = ws.Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not r Is Nothing Then Application.Goto (Sheets(ws.Name).Range(r.Address))

If Not r Is Nothing Then
Answer = MsgBox("Found on " & ActiveSheet.Name & " Continue searching ? ", vbYesNo)
If Answer = vbNo Then Exit Sub
Else
MsgBox "Not found on " & ws.Name

End If
Next ws
End Sub

 

[storm8]

hi,

I forgot .Find only searches first occurence
you have to use .FindNext for all other occurences.

do while not r is nothing
set r = r.findnext(r)
loop

(I think it should be like this, I have not really test it)

 

[DomiDarko]

hi,

I forgot .Find only searches first occurence
you have to use .FindNext for all other occurences.

do while not r is nothing
set r = r.findnext(r)
loop

(I think it should be like this, I have not really test it)

Thank you. This makes sense. I have been trying this code but not quite sure where to put it. I have tried a few things but so far it has crashed.

 

[storm8]

Option Explicit


Sub find()


    Dim ws As Worksheet
    Dim search As String
    search = "karel"
    
    For Each ws In ThisWorkbook.Worksheets
        Dim r As Range
        Dim f1 As Range
        If ws.Name = ThisWorkbook.Sheets(1).Name Then GoTo nws
        Set r = ws.Cells.find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not r Is Nothing Then
            Application.Goto (Sheets(ws.Name).Range(r.Address))
            MsgBox "found on " & ws.Name
                                 
            Set f1 = r
            Do While Not r Is Nothing
                Set r = ws.Cells.FindNext(r)
                
                Application.Goto (Sheets(ws.Name).Range(r.Address))
                If f1.Address = r.Address Then GoTo nws
                MsgBox "found again on " & ws.Name
            Loop
            
        Else
            MsgBox "not found on " & ws.Name
        End If
nws:
    Next ws
End Sub

 

[DomiDarko]

Thanks again!
This is getting quite advanced and a bit out of my understanding.
It is searching the whole sheet now but it loops back to first occurance again before going to the nws.
It also jumps back to the active sheet after searching other sheets. Confused!
I will try and alter it but I don't think I will be able to fix it.
Let me know if you have any luck!

its also still not skipping the first sheet but that's not a problem really.

 

[storm8]

hi,

I added some comments to the code so you can see what's going on.
Notice I switched two lines to prevent looping on ws
Im not sure why first ws is being evaluated, there's a tip in my code how to solve it

Option Explicit


Sub find()


    Dim ws As Worksheet
    Dim search As String
    search = "karel" 'the search string, change appropriately
    
    For Each ws In ThisWorkbook.Worksheets 'for each sheet in workbook
        
        Dim r As Range
        Dim f1 As Range
        
        If ws.Name = ThisWorkbook.Sheets(1).Name Then GoTo nws 'must skip sheet if first is currently evaluated. If not, they might be somehow mixed. place the name of first sheet instead
                        'like this
                            'If ws.Name = "Sheet 1" Then GoTo nws
                                                
        Set r = ws.Cells.find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 'finds first match
        
        
        If Not r Is Nothing Then 'something is found
            
            Application.Goto (Sheets(ws.Name).Range(r.Address)) 'goto result
            MsgBox "found on " & ws.Name 'msgbox
                                 
            Set f1 = r 'remember first found address
            
            'loop trough all the results. R is now address of first match, will look for other while we reach address of first match (now stored in r1)
            Do While Not r Is Nothing
                Set r = ws.Cells.FindNext(r) 'set r = next match
                
                If f1.Address = r.Address Then GoTo nws 'check if the find is not looping trough the heet, if yes, go to next ws
                Application.Goto (Sheets(ws.Name).Range(r.Address)) 'select the range
                MsgBox "found again on " & ws.Name 'tell me about it
            Loop
            
        Else
            MsgBox "not found on " & ws.Name
        End If
nws:
    Set r1 = Nothing 'reset first found
    Next ws 'just a pointer where the code can be pointed
End Sub

 

[DomiDarko]

That's great. Many thanks. I'll be working on this tmrw and until I perfect it. All your help is very much appreciated. I am learning fast.