+ Reply to Thread
Results 1 to 2 of 2

how can i copy/paste the word which is found during search in the current row wise

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    how can i copy/paste the word which is found during search in the current row wise

    this code working but can i paste which word was found in the Row to its opposite Row

    i my excel in column F

    i have list of words which is to be serached and paste the which word found in the row(colored yellow)

    IT IS POSSIBLE TO DO LIKE THIS

    example like:

    if i found the word CABLE in the row

    D E

    ELECTRICAL,CABLE, EXTENSION, 12V, 5 METER | CABLE
    BELT, V, 376IN OC, 0.65625IN WD, 0.40625IN THK | BELT


    Sub FindAndColor()
    
        Dim cell        As Variant
        Dim FndWhat     As String
        Dim RegExp      As Object
        Dim RngBeg      As Range
        Dim RngEnd      As Range
        Dim SearchWords As Variant
        Dim Word        As Variant
        
        
            Set RngBeg = Range("F2")
            Set RngEnd = Cells(Rows.Count, "F").End(xlUp)
            If RngEnd.Row < RngBeg.Row Then Exit Sub
            
            SearchWords = Range(RngBeg, RngEnd).Value
            
            Set RngBeg = Range("D2")
            Set RngEnd = Cells(Rows.Count, "D").End(xlUp)
            If RngEnd.Row < RngBeg.Row Then Exit Sub
            
            
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.IgnoreCase = True
            
            Application.ScreenUpdating = False
            
                For Each Word In SearchWords
                    RegExp.Pattern = "\b" & Word & "\b"
                    For Each cell In Range(RngBeg, RngEnd)
                        If RegExp.Execute(cell).Count > 0 Then
                            cell.Interior.ColorIndex = 6
                            cell.Interior.Pattern = xlSolid
                        End If
                    Next cell
                Next Word
            
            Application.ScreenUpdating = True
            
    End Sub
    Last edited by baig123; 10-17-2014 at 07:21 AM. Reason: paragraph

  2. #2
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    Re: how can i copy/paste the word which is found during search in the current row wise

    i have added some line of code after this line If RngEnd.Row < RngBeg.Row Then Exit Sub

    adding some line it is copying entire column of D

    but i am trying to display only which full word was found in D reference to the F Column

    should be displayed Only search words in E Column

    i have attached file

    Sub FindAndColor()
    
        Dim cell        As Variant
        Dim FndWhat     As String
        Dim RegExp      As Object
        Dim RngBeg      As Range
        Dim RngEnd      As Range
        Dim SearchWords As Variant
        Dim Word        As Variant
        
        
            Set RngBeg = Range("F2")
            Set RngEnd = Cells(Rows.Count, "F").End(xlUp)
            If RngEnd.Row < RngBeg.Row Then Exit Sub
            
            SearchWords = Range(RngBeg, RngEnd).Value
            
            Set RngBeg = Range("D2")
            Set RngEnd = Cells(Rows.Count, "D").End(xlUp)
            If RngEnd.Row < RngBeg.Row Then Exit Sub
            
             With Range("D2:D800")
    '         .AutoFilter Field:=7, Criteria1:="=*Line*"
            Intersect(Range("D2:D" & Rows.Count), .SpecialCells(xlCellTypeVisible).EntireRow).Copy _
            Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
            End With
            'Selection.AutoFilter
            
            Set RegExp = CreateObject("VBScript.RegExp")
            RegExp.IgnoreCase = True
            
            Application.ScreenUpdating = False
            
                For Each Word In SearchWords
                    RegExp.Pattern = "\b" & Word & "\b"
                    For Each cell In Range(RngBeg, RngEnd)
                        If RegExp.Execute(cell).Count > 0 Then
                            cell.Interior.ColorIndex = 6
                            cell.Interior.Pattern = xlSolid
                        End If
                    Next cell
                Next Word
            
            Application.ScreenUpdating = True
            
    End Sub
    Attached Files Attached Files
    Last edited by baig123; 10-17-2014 at 07:20 AM. Reason: file change

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] looking to find 1 of 2 words in a cell in column B and return the word found in the same
    By Rob69mfj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2014, 01:17 PM
  2. [SOLVED] Copy row from one sheet and paste to another if a key word is found in 1 column of sheet 1
    By killdozer in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-17-2014, 02:27 PM
  3. [SOLVED] Macro to find every employee id found in column B and paste on matching row
    By walkingwill in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2013, 11:15 AM
  4. find and copy paste the value below found word
    By emina002 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2011, 05:39 AM
  5. How to search for a word in a column and have entire row opaque once word is found
    By copleyr in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 09-04-2009, 07:11 PM

Tags for this Thread

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