VBA Search/Find Functionality

[rob325114]

Hi,

I've been trying to build a search functionality in excel which acts very much like a web search engine.

I have roughly 5 sheets which hold information on a row by row basis. I need to be able to type a keyword into a specified cell, then for every row that contains the keyword (in any string, in any cell in that row) I need that row to be inserted into a results sheet. What I'd essentially end up with is a single results page containing all the rows that contain the search term somewhere within them.

I've started adapting some code that I've collated from various places on the internet (lost sources - sorry!), but as a VB newbie, I'm struggling to get it to loop through entire rows and copy those rows into a results page.

Has anyone ever come across some existing code for this, or see how the below could be adapted for my purpose?

Thanks

Rob

Sub Basic_Excel_Search_by_Row()
  Dim DestSheet        As Worksheet
  Set DestSheet = Worksheets("Results Page")
  
  Dim Searchkeyword As String
  Set Searchkeyword = Sheets("User Interface").Range("C19").Value
  
  Dim sRow       As Long     'This is the row index on source worksheet
  Dim dRow       As Long     'This is the row index on destination worksheet
  Dim sCount     As Long
  sCount = 0
  dRow = 1
  For sRow = 1 To Range("D65536").End(xlUp).Row
  
     'See if the cell contains the search keyword.
     'I can only see how to do this on specific
     'cells at the moment, but need it to search the
     'entire row. Also considered adapting the instr()
     'function to see if the cell *contains* the search
     'term anywhere in the string.
     
     If Cells(sRow, "D") Like Searchkeyword Then
     
        sCount = sCount + 1
        dRow = dRow + 1
        'copy row
        Row(sRow).Copy Destination:=DestSheet.Row(dRow)
     End If
  Next sRow
  
  MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"
End Sub

 

[GlennUK]

Have you considered using Advanced Filter to do this? You can build a simple criteria block using formulas in a separate sheet, and direct the output straight to the Results sheet. The total code would be about 5 lines.

For criteria, have the same column headings as in the source data, and have 1 row of criteria for each field, and have the cell criteria be "*keyword*", or build that via formulas. So that's one non-blank criteria cell per row of criteria block.

Have the same column headings in the Results sheet.