+ Reply to Thread
Results 1 to 2 of 2

Output text to cells if search result is positive

Hybrid View

  1. #1
    Registered User
    Join Date
    07-28-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    5

    Output text to cells if search result is positive

    Greetings everyone, I'm self-taught and I know my code is garbage but it works. Basically I have a spreadsheet with filenames in a row, and keywords on the row to it's right. I created a working UserForm that will color the lines where the searched text was found. The user can enter up to 3 words to search and the lines will be colored if either of these words are found. I also added the blank textbox check to prevent coloring every empty line. Lastly, I have a working reset button to return everything to their default colors.

    So I'm pretty happy about all of this as it's my first time playing with code.

    However, it would be better if instead of coloring the lines where the text is found, if the code would start outputting the filenames somewhere for all the positive search results.

    In column G I have my filenames, and column H I have thekeywords that get searched.

    I was thinking about picking a random case left of my data, say B3, and list the filesnames starting from there for all the positive matches. That means if 3 matches were found, B3, B4 and B5 would have the corresponding filenames in them.

    Here is my code so far, I know I probably could've used a "for i = 1 to 3" loop but I didn't even know the right syntax in this programming language.

      
    Private Sub CommandButton1_Click()
    
         
        Dim SrchRng3 As Range
        Dim c3 As Range, f As String
        Dim Recherche1, Recherche2, Recherche3 As String
        
        Recherche1 = TextBox1.Text
        Recherche2 = TextBox2.Text
        Recherche3 = TextBox3.Text
    
    
    
            If Not Recherche1 = "" Then
                Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp))
                Set c3 = SrchRng3.Find(Recherche1, LookIn:=xlValues)
                If Not c3 Is Nothing Then
                    f = c3.Address
                    Do
                        With ActiveSheet.Range("G" & c3.Row & ":H" & c3.Row)
                            .Font.ColorIndex = 2
                            .Interior.ColorIndex = 53
                        End With
                        Set c3 = SrchRng3.FindNext(c3)
                    Loop While c3.Address <> f
                End If
            End If
         
            If Not Recherche2 = "" Then
                Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp))
                Set c3 = SrchRng3.Find(Recherche2, LookIn:=xlValues)
                If Not c3 Is Nothing Then
                    f = c3.Address
                    Do
                        With ActiveSheet.Range("G" & c3.Row & ":H" & c3.Row)
                            .Font.ColorIndex = 2
                            .Interior.ColorIndex = 53
                        End With
                        Set c3 = SrchRng3.FindNext(c3)
                    Loop While c3.Address <> f
                End If
            End If
            
            If Not Recherche3 = "" Then
                Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp))
                Set c3 = SrchRng3.Find(Recherche3, LookIn:=xlValues)
                If Not c3 Is Nothing Then
                    f = c3.Address
                    Do
                        With ActiveSheet.Range("G" & c3.Row & ":H" & c3.Row)
                            .Font.ColorIndex = 2
                            .Interior.ColorIndex = 53
                        End With
                        Set c3 = SrchRng3.FindNext(c3)
                    Loop While c3.Address <> f
                End If
            End If
    
    
    End Sub

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Output text to cells if search result is positive

    Hello pylauzier,

    Welcome to the Forum!

    Here is your converted macro. If you have more text boxes, just add then to the srchArray. All the matching results are listed starting in cell B3.
    Private Sub CommandButton1_Click()
    
        Dim c3 As Range
        Dim DstRange As Range
        Dim f As String
        Dim Item As Variant
        Dim R As Long
        Dim srchArray As Variant
        Dim SrchRng3 As Range
        
            Set DstRange = ActiveSheet.Range("B3")
      
            Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp))
            
            srchArray = Array(TextBox1.Text, TextBox2.Text, TextBox3.Text)
        
    
              For Each Item In srchArray
                Set c3 = SrchRng3.Find(Item, , xlValues, xlWhole, xlByRows, xlNext, False)
                If Not c3 Is Nothing Then
                   f = c3.Address
                     Do While Not c3 Is Nothing
                       DstRange.Offset(R, 0) = c3.Value
                       R = R + 1
                       Set c3 = SrchRng3.FindNext(c3)
                       If c3.Address = f Then Exit Do
                     Loop
              Next Item
    
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1