Yet Another Complicated Search Code

nadalwannabe

New Member
Joined
May 28, 2010
Messages
4
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

New Member
Joined
May 28, 2010
Messages
4
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
Hello

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

Code:
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

New Member
Joined
May 28, 2010
Messages
4
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

New Member
Joined
May 28, 2010
Messages
4
wigi,

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

Thanks!

Parker
 

nadalwannabe

New Member
Joined
May 28, 2010
Messages
4
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
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.
 

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top