If the chain of characters found, then...

  • Thread starter Thread starter Souriane
  • Start date Start date
S

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