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

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

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


"Souriane" wrote:

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

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





"Souriane" wrote in message
...
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

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joel" wrote:

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

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


"Souriane" wrote:

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

Or this way also...

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

--
Rick (MVP - Excel)


"JLGWhiz" wrote in message
...
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





"Souriane" wrote in message
...
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