Find text and copy row

[fari1]

hi, my below code finds the text "consolidated" and in sheet3 list all the cells that contain consolidated. i want this code to copy the whole instead of that cell which contains consolidated and gives the output in sheet3.e.g

      A             B            C                    D                  E
        Consolidated                 sheets             result

with reference to the above example i want to get the complete row copied as a result of this code in sheet3.the code is below

Sub findTEXT()
Dim f As Range, fa As String, i As Long
Dim src As Worksheet, dst As Worksheet
Set src = Sheets("sheet2") 'sheet to be searched, change as required
Set dst = Sheets("sheet3") 'sheet for output, change as required
i = 1
With dst
    Set f = src.Cells.Find(What:="CONSOLIDATED", After:=src.Cells(1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
If Not f Is Nothing Then
fa = f.Address
Do
    If Len(f.Value) < 50 Then
    .Range("A" & Rows.Count).End(xlUp)(2) = f.Value
    .Range("B" & Rows.Count).End(xlUp)(2) = src.Range("A1").Value
i = i + 1
End If
    Set f = src.Cells.FindNext(f)
    Loop Until fa = f.Address
End If
End With
End Sub

 

[fari1]

in above example text is getting mixed, every text is in a separate cell

 

[Warship]

I'm not sure I understand what you need to accomplish.

Are you saying that for every row in sheet2 that has "CONSOLIDATED",
you want that row put on sheet3?

Post sheet example of sheet2 and sheet example of what you need to end up with.

Why the "If Len(f.Value) < 50" ?

 

[fari1]

here's the example of sheet2

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:209px;" /><col style="width:300px;" /><col style="width:568px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4939</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4940</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4941</td><td >CONSOLIDATED FINANCIAL STATEMENTS </td><td > </td><td >Description</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4942</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4943</td><td >CITIGROUP INC. AND SUBSIDIARIES </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4944</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4945</td><td >CONSOLIDATED STATEMENT OF INCOME (Unaudited) </td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


in above i want to copy the whole row, in which any cell that contain the word consolidated, in above case row 4941 and row 4945 i want.

and i dun want the rows where len is greater than 50, in the code that i provided, the len is for one particular cell than contains the word consolidated, can u make it work, to get the len of whole row, and see the len of all the cells in that row and then see if it is greater than 50 then dun give that row as output

 

[Warship]

Do we only need to search fo "consolidated" in Col A?

 

[fari1]

yes just for consolidated

 

[fari1]

not just in column A, in whole sheet

 

[tigeravatar]

fari1,

Give the following a try:

Sub findTEXT()
    
    Static wsSrc As Worksheet: Set wsSrc = ActiveWorkbook.Sheets("Sheet2")
    Static wsDst As Worksheet: Set wsDst = ActiveWorkbook.Sheets("Sheet3")
    
    Dim rngFnd As Range
    Dim rngAll As Range
    Dim allfound As Boolean
    
    Set rngFnd = wsSrc.UsedRange.Find("consolidated")
    While Not rngFnd Is Nothing And allfound = False
        If rngAll Is Nothing Then Set rngAll = rngFnd
        Set rngFnd = wsSrc.UsedRange.Find("consolidated", rngFnd)
        If Intersect(rngAll, rngFnd) Is Nothing Then
            If Intersect(rngAll.EntireRow, rngFnd) Is Nothing Then Set rngAll = Union(rngAll, rngFnd)
        Else
            allfound = True
        End If
    Wend
    If Not rngAll Is Nothing Then rngAll.EntireRow.Copy wsDst.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    
End Sub

Hope that helps,
~tigeravatar

 

[fari1]

hi, thanks for for the code, u didn't define the LEN in the code, i dont want those rows, where LEN of the total row (where one of the cell contains consolidated) is more than 50.

 

[tigeravatar]

fari1,

So in a row that contains "consolidated", if all the cells' text combined exceeds 50 characters then you do not want that row?