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