Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Search function code question

  1. #1
    Join Date
    28th April 2012
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd November 2010
    Posts
    92

    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


  3. #3
    Join Date
    28th April 2012
    Posts
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th April 2012
    Posts
    3

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd November 2010
    Posts
    92

    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


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Keyword String Search Question
    By daniel24 in forum EXCEL HELP
    Replies: 3
    Last Post: March 11th, 2011, 11:56
  2. search/replace question
    By cyeung in forum Excel and/or Word Help
    Replies: 1
    Last Post: June 8th, 2006, 18:02
  3. LOOKUP function question
    By david747 in forum EXCEL HELP
    Replies: 6
    Last Post: October 22nd, 2004, 07:17
  4. Advanced search question with VBA
    By amir904 in forum EXCEL HELP
    Replies: 2
    Last Post: July 10th, 2004, 02:00

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno