Announcement

Collapse
No announcement yet.

Search function code question

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Search function code question

    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?

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

  • #2
    Re: Search function code question

    How about something like this?

    Code:
        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
    Live Long and Prosper

    Ted

    sigpic

    Comment


    • #3
      Re: Search function code question

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

      Comment


      • #4
        Re: Search function code question

        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.

        Comment


        • #5
          Re: Search function code question

          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:

          Code:
          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
          Live Long and Prosper

          Ted

          sigpic

          Comment

          Working...
          X