If the chain of characters found, then...

[Souriane]

Hi all!

The following macro check if there is a "1" in column "E". If so, it
copies the line in sheet2, if not, then nothing.

I would like the same kind of macro but for the chain of characters
"CA" that may appear in the words in the cells.

Can you help me?! Thank you!

Dim wsS As Worksheet, wsD As Worksheet
Dim lr As Long, i As Long, j As Long
Set wsS = Sheets("Sheet1")
Set wsD = Sheets("Sheet2")
lr = wsS.Cells(Rows.Count, "A").End(xlUp).Row
j = 1
For i = 1 To lr
If wsS.Cells(i, 5) = 1 Then
wsS.Rows(i).Copy wsD.Rows(j)
j = j + 1
End If
Next i

Souriane

 

[John Bundy]

Sub main()
Dim wsS As Worksheet, wsD As Worksheet
Dim lr As Long, i As Long, j As Long
Set wsS = Sheets("Sheet1")
Set wsD = Sheets("Sheet2")
lr = wsS.Cells(Rows.Count, "E").End(xlUp).Row
j = 1
For i = 1 To lr
If InStr(1, wsS.Cells(i, 5), "ca", vbTextCompare) > 0 Then
wsS.Rows(i).Copy wsD.Rows(j)
j = j + 1
End If
Next i
End Sub

 

[Joel]

from
If wsS.Cells(i, 5) = 1 Then

to
If Instr(wsS.Cells(i, 5), "CA") > 0 Then

 

[JLGWhiz]

This will find it if the CA in the search range is upper case. It will not
find it if the CA is lower case. To find it in either case, change this
line:

If wsS.Cells(i, 5) Like "*CA*" Then

To this:

If UCase(wsS.Cells(i, 5)) Like "*CA*" Then

----------------------

Dim wsS As Worksheet, wsD As Worksheet
Dim lr As Long, i As Long, j As Long
Set wsS = Sheets("Sheet1")
Set wsD = Sheets("Sheet2")
lr = wsS.Cells(Rows.Count, "A").End(xlUp).Row
j = 1
For i = 1 To lr
If wsS.Cells(i, 5) Like "*CA*" Then
wsS.Rows(i).Copy wsD.Rows(j)
j = j + 1
End If
Next i

 

[ryguy7272]

Slightly different way:
Sub newone()
Dim RngColE As Range
Dim i As Range
Dim Dest As Range
Sheets("Sheet1").Select
Set RngColE = Range("E1", Range("E" & Rows.Count).End(xlUp))
With Sheets("Sheet2")
Set Dest = .Range("A1")
End With
For Each i In RngColE
If i.Value = "CA" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


HTH,
Ryan---

 

[Rick Rothstein]

Or this way also...

If wsS.Cells(i, 5) Like "*[Cc][Aa]*" Then