Results displayed without headers

[Tharabai]

Hi,

i have used the below code to search the list of words i have listed in another. Code works for me except for the header. The first result is to be copied with the header data and then after the results are pasted after the last used without header data.

can some shed some inputs on this pls..

Sub search() 
  
  lastSrc_rw = Sheets("Keyword").Range("A" & Rows.Count).End(xlUp).Row 
  MsgBox lastSrc_rw 
  
  For Each ksearch In Sheets("Keyword").Range("A1:A" & lastSrc_rw) 
  With Sheets("Sheet1").Columns(53) 
  Set c = .Find(car, LookIn:=xlValues, lookat:=xlPart) 
  If Not c Is Nothing Then 
  firstAddress = c.Address 
  Do 
  
  nxtDst_rw = Sheets("Keyword_Result").Range("A" & Rows.Count).End(xlUp).Row + 1 
  c.EntireRow.Copy Destination:=Sheets("Keyword_Result").Range("A" & nxtDst_rw) 
  Set c = .FindNext(c) 
  Loop While Not c Is Nothing And c.Address <> firstAddress 
  End If 
  End With 
  Next 
End Sub

 

[NARAYANK991]

Hi ,

Why do you need to associate the copying of the header row with the first result of the Find operation ? Can you not just copy the header before you enter the For ... Next loop ?

Narayan

 

[Tharabai]

I am inserting a new worksheet and pasting the results after keyword search. If the header can be copied before searching also fine for me.. Pls let me know what code to be inserted.

 

[NARAYANK991]

Hi ,

Since I don't have the workbook , try this :

Sub search()
    Dim lastSrc_rw As Long
    Dim datarange As Range, ksearch As Range
 
    lastSrc_rw = Sheets("Keyword").Range("A" & Rows.Count).End(xlUp).Row
    MsgBox lastSrc_rw
 
    Set datarange = Sheets("Keyword").Range("A1:A" & lastSrc_rw)
    Sheets("Keyword_Result").Range("A1").Resize(1, 53).Value = Sheets("Sheet1").Range("A1").Resize(1, 53).Value
    For Each ksearch In datarange
        With Sheets("Sheet1").Columns(53)
             Set c = .Find(ksearch, LookIn:=xlValues, lookat:=xlPart)
             If Not c Is Nothing Then
                firstAddress = c.Address
             
                Do
                   nxtDst_rw = Sheets("Keyword_Result").Range("A" & Rows.Count).End(xlUp).Row + 1
                   c.EntireRow.Copy Destination:=Sheets("Keyword_Result").Range("A" & nxtDst_rw)
                   Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
             End If
        End With
    Next
End Sub

Narayan

 

[Tharabai]

Hi Again,

Still it is copying the values with the header from sheet1.

Also, can we highlight the results in the sheet1 for each keyword and copy the colored rows at the last to Keyword_result sheet to avoid duplication of rows.

As of now, each keyword results are copied to Result sheet then and there which results in duplication. Ex, keyword award and awards will provide the same results which is duplicate.