Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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?

    VB:
    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?

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

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