Thread: Search Macro

This is my first time writing a macro, although I took a Visual Basic class before. Any help would be greatly appreciated.

I have several vehicles each in their own tab. In each tab, there are assessments listed, and then equipment in the vehicle at time of assessment. What I need to do is create a way to click any equipment's part number, and search through the entire file to make a list of all vehicles that contained the same equipment, and at which assessment.

Is this something that a macro can help with?

Thanks ahead of time

Attached is a very simplified layout of how the data is organized. Anything else I can do to help clarify?

Also, there are going to be many many part numbers, so for now I think place the results in a .txt file?

Assessment data will be unique, but if the solution includes the block as well, I certainly won't be upset =)

Just to keep things interesting, there's been a little change. There's going to be a cover tab that will have a igoogle-like layout with a search tool, and the results will be shown in that tab under the search. Thanks guys for your patience

Alright well here's what I have so far I guess:

Public Sub Search()

Range("C13:P45") = ""                                                           'Clear Old Search
DatatoFind = Range("D5")                                                        'Assign Search Term
If DatatoFind = "" Then Exit Sub                                                'Cancel Blank Searches

x = 14                                                                          'Start on row 14
For Each wkst In Worksheets                                                     'Loop Through Sheets
    If wkst.Name <> "Search" And _
    wkst.Name <> "Compliances" And _
    wkst.Name <> "Acronyms" Then                                                'Skip These Sheets
        Range("C" & x) = wkst.Name                                              'Display Platform
                
        With Worksheets(wkst.Name).Range("D:D")                                 'Set Range
            Set c = .Find(DatatoFind)                                           'Find Data
                If Not c Is Nothing Then                                        'If Found
                    firstAddress = c.Address                                    'Get first terms address
                    Do
                        Worksheets("Search").Range("E" & x) = c                 'Display in search
                        Set c = .FindNext(c)                                    'Find Next Data
                        x = x + 1                                               'Next row
                    Loop While Not c Is Nothing And c.Address <> firstAddress   'Loop until entire sheet is complete
                End If
        End With
        x = x + 1                                                               'Next row

    End If
Next wkst

End Sub

All thats displayed are the tab names right now. "If not c is nothing" never passes. I'm not sure if I'm searching right... can someone help?

So my search was working it was just the dashes messing it up. I have more problems though:

Public Sub Search()

Range("C13:P45") = ""                                                           'Clear Old Search
DatatoFind = Range("D5")                                                        'Assign Search Term
If DatatoFind = "" Then Exit Sub                                                'Cancel Blank Searches

x = 14                                                                          'Start on row 14
For Each wkst In Worksheets                                                     'Loop Through Sheets
    If wkst.Name <> "Search" And _
    wkst.Name <> "Compliances" And _
    wkst.Name <> "Acronyms" Then                                                'Skip These Sheets
   
    With Worksheets(wkst.Name).Range("D:D")                                 'Set Range
        Set c = .Find(DatatoFind)                                           'Find Data
            If Not c Is Nothing Then                                        'If Found
                firstAddress = c.Address                                    'Get first terms address
                Do
                    Worksheets("Search").Range("C" & x) = wkst.Name         'Display Platform
                    Worksheets("Search").Range("E" & x) = c.Offset(0, -3)   'Display Block
                    Worksheets("Search").Range("G" & x) = c.Offset(0, -2)   'Display Assessment
                    Worksheets("Search").Range("J" & x) = c.Offset(0, -1)   'Display Name
                    Worksheets("Search").Range("M" & x) = c                 'Display P/N
                    Set c = .FindNext(c)                                    'Find Next Data
                    x = x + 1                                               'Next row
                Loop While Not c Is Nothing And c.Address <> firstAddress   'Loop until entire sheet is complete
            End If
    End With
    x = x + 1                                                               'Next row
    
    End If
Next wkst

End Sub

Now the block and assessment date are in merged cells, and offsetting is not getting the values of the cells unless they are in the first row because that's where the merged cell value is... is there a way to adjust for this?

I also only want the first line for these merged cells since i used a line break alt + enter

So things I need help with:

• Ignore the formatting when displaying to search
  Ignore dashes while searching
  Getting Merged Cell Values
  Getting only the first line in some cases

Well I guess I'll just keep documenting my work... lol

Public Sub Search()

Range("C15:P50") = ""                                                                   'Clear Old Search
DatatoFind = Range("D5")                                                                'Assign Search Term
If DatatoFind = "" Then Exit Sub                                                        'Cancel Blank Searches
Range("D11") = "Last Search: " & DatatoFind                                             'Show Search Term

x = 15                                                                                  'Start on row 15
For Each wkst In Worksheets                                                             'Loop Through Sheets
    If wkst.Name <> "Search" And _
    wkst.Name <> "Compliances" And _
    wkst.Name <> "Acronyms" Then                                                        'Skip These Sheets
   
    With Worksheets(wkst.Name).Range("D:D")                                             'Set Range
        Set c = .Find(DatatoFind)                                                       'Find Data
        If Not c Is Nothing Then                                                        'If Found
            firstAddress = c.Address                                                    'Get first terms address
            Do
                With Worksheets("Search")
                    .Range("C" & x) = Worksheets(wkst.Name).Range("A1")                 'Display Platform
                    .Range("E" & x) = c.Offset(0, -3).MergeArea(1, 1)                   'Display Block
                    .Range("E" & x) = Replace(.Range("E" & x), Chr(10), "   ")
                    .Range("G" & x) = c.Offset(0, -2).MergeArea(1, 1)                   'Display Date
                    .Range("G" & x) = Replace(.Range("G" & x), Chr(10), "   ")
                    .Range("H" & x) = c.Offset(0, -1)                                   'Display Name
                    .Range("H" & x) = Replace(.Range("H" & x), Chr(10), "   ")
                    .Range("O" & x) = c                                                 'Display P/N
                    .Range("O" & x) = Replace(.Range("O" & x), Chr(10), "   ")
                    .Range(x & ":" & x).WrapText = False                                'No word wrap
                End With
                Set c = .FindNext(c)                                                    'Find next data
                x = x + 1                                                               'Next row
                If x = 50 Then
                    Range("H50") = "Too many results, please be more specific."         'Too many results
                    Exit Sub
                End If
                                        
            Loop While Not c Is Nothing And c.Address <> firstAddress                   'Loop through entire sheet
        End If
    End With
   
    End If
Next wkst

If Range("C15") = "" Then Range("H15") = "No Search Results Found."                     'No results

End Sub

So I still haven't been able to ignore the dashes since wildcards dont work as far as my google searches have told me.
I've subsituted line breaks for spaces, but I can't only get the first line (without wildcards again) so I've instead adjusted the column size since most of the dates are around the same pixel width.
Fixed formating with a word wrap property
Found how to get merged cell data

...I don't know if anyone will see this though =(

I don't want to be rude, but can someone please help me out?

Soooo, I'm guessing that's a no?