Creating a Search Field

[stully]

This is my first time trying to write a macro and I am having a lot of trouble with it. I could really use some help writing this code properly.

I am trying to create a search field that will search multiple worksheets and bring that data back to a range of cells. The customer will put in a product into cell B1, hit enter, and the results will be displayed starting at cell B2. It needs to search column A on 6 different worksheets.

Please help if you can! Thank you!

 

[gallen]

You haven't provided enough info.

If it finds the value in column A which cells does it copy across? Just the cell with the found value or some adjacent ones?

Are you searching all other sheets in the workbook? If not what are the names of the sheet that will have the results and the sheets that you are searching? (If these are the only sheets, names aren't needed as you can just loop through & search all sheets, ignoring the results sheets)

 

[stully]

I'm sorry - thank you for replying!
So the customer will put in cell B2 on the "Instructions" worksheet an item they are searching for. When they hit "enter" it will search column A in all other worksheets (except for Instructions worksheet) - there are 7 different worksheets ("nursing", "ALS", "YAH", "Culinary", "Env Svs", "Maint." "Beauty"). When it finds a match/matches, it will bring back just the data that's in column A (no adjacent cells). It will list the found items starting at cell B3 through B8.

I hope that's enough information.. If not, please let me know!

 

[gallen]

This doesn't contain error handling, but it does work. Place this in a module and call it when the user changes cell B1.

Sub DoSearch()
    Dim wsResults As Worksheet, ws As Worksheet
    Dim sFirstAddress As String
    Dim sSearch As String
    Dim rFind As Range
    Dim rSearch As Range
    Dim nr As Long 'next row to paste found result to
    Dim lr As Long 'last used row on ColB of Instructions sheet
    
    Set wsResults = Worksheets("Instructions")
    
    nr = 3 'first row to paste found result
    
    lr = wsResults.Range("B" & Rows.Count).End(xlUp).Row
    
    sSearch = wsResults.Range("B2").Value 'store search value
    
    'dont continue if nothing in search
    If Len(sSearch) = 0 Then Exit Sub
    
    'Clean down cells.
    If lr > 2 Then wsResults.Range(wsResults.Range("B3"), wsResults.Range("B" & lr)).ClearContents
    
    
    'loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsResults.Name Then
            'serach range is column A
            Set rSearch = ws.UsedRange.Columns(1)
            Set rFind = rSearch.Find(What:=sSearch, lookat:=xlPart)
            If Not rFind Is Nothing Then 'We have a match
                sFirstAddress = rFind.Address
                'Loop through finding every instance
                Do
                    'store the result
                    wsResults.Range("B" & nr).Value = rFind.Value
                    'set next row
                    nr = nr + 1
                    'find next instance
                    Set rFind = rSearch.FindNext(rFind)
                    'if we aren't back where we started, go again.
                Loop Until rFind.Address = sFirstAddress
            End If
        End If
    Next ws


End Sub

 

[stully]

I am not sure what I am doing wrong. I pasted the above into Module1 and ran the module, no errors, but when I tried to search for a product, nothing came up in the results field.
When you say "call it when the user changes B1" is there something besides "Enter" that they should do?
THANK YOU for the assistance!

 

[gallen]

I am not sure what I am doing wrong. I pasted the above into Module1 and ran the module, no errors, but when I tried to search for a product, nothing came up in the results field.
When you say "call it when the user changes B1" is there something besides "Enter" that they should do?
THANK YOU for the assistance!

Sorry, only just seen this.

Lets ensure it is working before we worry about how it is triggered.

Make sure you put a value in cell B2 of the 'Instructions' worksheet, that you know SHOULD be found. Press Alt+F8 and run the Macro. Does it work?

 

[stully]

Hello, Thank you for getting back to me. It does NOT bring up the results - I entered in a product that is currently on the 4th worksheet in column A, but it did not bring the result back to the "Instructions" worksheet. I tried just hitting enter, I tried Alt+F8, but nothing worked. I wonder if it would be easier if I created a Search Button and then when that button is pushed it runs the macro?

 

[gallen]

It works fine for me.

Go to your 'Instructions' worksheet and press Alt+F11

Paste this code into the window that appears.

See if it works

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("B2").Address Then
        DoSearch
    End If
End Sub

 

[stully]

It does sort of work now... is there a way that it can also bring over the hyperlink that is attached to the products?

 

[gallen]

Try changing this line:

[COLOR=#333333]wsResults.Range("B" & nr).Value = rFind.Value[/COLOR]

to:

rFind.Copy wsResults.Range("B" & nr)