Yet Another Complicated Search Code

[nadalwannabe]

Good Afternoon All,

My name is Parker and I recently started a new job working in the aviation industry. My first big project that I was given to work on has been excel based and I don't have all that much experience with vba... I have been search on the web and have picked up on a lot of things, but I just can't get everything figured out for what is needed to complete the project. ANY help would be greatly accepted.

Okay on to my problem... I have a workbook that has data to several companies that can do repairs on our product. on each one of their "tabs" has a list of every repair that is possible, with a simple "yes" or "no" next to it. What my boss wants is the title worksheet to have a search box pop up and be able to search the repair code (ex B0001) or the Company name and have the results come up.

This has been an dreadful task for me as this has been the only thing I've been working on for the last month. If more information is required I'd be happy to post an example file or explain in more detail what is going on.

Thank you all so very much,

Parker

 

[twoplustwo]

Good Afternoon All,

My name is Parker and I recently started a new job working in the aviation industry. My first big project that I was given to work on has been excel based and I don't have all that much experience with vba... I have been search on the web and have picked up on a lot of things, but I just can't get everything figured out for what is needed to complete the project. ANY help would be greatly accepted.

Okay on to my problem... I have a workbook that has data to several companies that can do repairs on our product. on each one of their "tabs" has a list of every repair that is possible, with a simple "yes" or "no" next to it. What my boss wants is the title worksheet to have a search box pop up and be able to search the repair code (ex B0001) or the Company name and have the results come up.

This has been an dreadful task for me as this has been the only thing I've been working on for the last month. If more information is required I'd be happy to post an example file or explain in more detail what is going on.

Thank you all so very much,

Parker

Hi,

Excel is not a database. I'd stick it in Access.

 

[wigi]

Hello

Select the cell you want to look up in all other sheets, and run this macro:

Sub SearchOnAllSheets()
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then
            Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 2) = _
                Split(ws.Name & "|" & ws.Columns(1).Find(ActiveCell.Value, , xlValues, xlWhole).Offset(, 1), "|")
        End If
    Next
End Sub

It will list all found instances next to each other.

Wigi

 

[nadalwannabe]

Thanks for the quick reply!!!

I never really thought about doing this project in Access so I will def. look into it even though I have almost no exp with it.

If I do move it into access will the data still be able to be changed and updated as needed?

 

[nadalwannabe]

wigi,

that helps a ton... I will look into it and let you know how it goes.

Thanks!

Parker

 

[wigi]

If I do move it into access will the data still be able to be changed and updated as needed?

Wouldn't that be a prerequisite of any database program?

 

[nadalwannabe]

Wigi,

yes that is very true... Let's just say i'm def ready for the holiday weekend.

as for your code. It works great for returning the first occurance of the searched value. The problem with it is there are the same values that must be returned on several sheets after that; which is where I ran into problems. I always assumed that I would have to run a code like the one you gave me with some sort of loop to make that happen.

 

[wigi]

Like I used Find, FindNext can search for more than one occurrence. The VBA helpfiles on FindNext show an example, complete code is given there.