Search function code question

[diamonddave]

I have the following code in a sheet and when i want to search for a term i have got to go into the code and replace the keyword. how can i have it so that i can put the search term in a cell and it will search for that?

Sub SearchForString() 
    Dim LSearchRow As Integer 
    Dim LCopyToRow As Integer 
     
    On Error Goto Err_Execute 
     
     'Start search in row 4
    LSearchRow = 4 
     
     'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2 
     
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0 
         
         'If value in column E = "Mail Box", copy entire row to Sheet2
        If Range("A" & CStr(LSearchRow)).Value = "4" Then 
             
             'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select 
            Selection.Copy 
             
             'Paste row into Sheet2 in next row
            Sheets("Sheet2").Select 
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select 
            ActiveSheet.Paste 
             
             'Move counter to next row
            LCopyToRow = LCopyToRow + 1 
             
             'Go back to Sheet1 to continue searching
            Sheets("Sheet1").Select 
             
        End If 
         
        LSearchRow = LSearchRow + 1 
         
    Wend 
     
     'Position on cell A3
    Application.CutCopyMode = False 
    Range("A3").Select 
     
    MsgBox "All matching data has been copied." 
     
    Exit Sub 
     
Err_Execute: 
    MsgBox "An error occurred." 
     
End Sub 

[tkowal]

How about something like this?

Dim LSearchRow As Integer 
Dim LCopyToRow As Integer 
Dim SearchString As String 
 
On Error Goto Err_Execute 
 
 'Grab your Search String
SearchString = Range("TheCellonyourSheet").Value 
 
 'Start search in row 4
LSearchRow = 4 
 
 'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2 
 
While Len(Range("A" & CStr(LSearchRow)).Value) > 0 
     
     'If value in column E = "Mail Box", copy entire row to Sheet2
    If Range("A" & CStr(LSearchRow)).Value = SearchString Then 
         'Select row in Sheet1 to copy

[diamonddave]

What about searching in column A where a cell may have multiple values and I want to show that on the result page.

[diamonddave]

Example:
I may have A6 with a single word or number: lets say "clinical"
but A10 might have "clinical", "practice", "Hospital"

and if i search for clinical i want both rows to show.

[tkowal]

Remember that I am doing this without the benifit of your data or circumstances so please test before using on real data....

you can do something like this:

Dim LSearchRow As Integer 
Dim LCopyToRow As Integer 
Dim SearchString As String 
 
On Error Goto Err_Execute 
 
 'Grab your Search String
SearchString = Range("TheCellonyourSheet").Value 
 
 'Start search in row 4
LSearchRow = 4 
 
 'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2 
 
While Len(Range("A" & CStr(LSearchRow)).Value) > 0 
     
     'If value in column E = "Mail Box", copy entire row to Sheet2
     'If Range("A" & CStr(LSearchRow)).Value = instr(SearchString Then
     'This will search A for the existance of SearchString used as a substring
    If InStr(1, Range("A" & CStr(LSearchRow)).Value, SearchString) > 0 Then 
         'Select row in Sheet1 to copy