Macro - reads cells in a column .If keyword found moves cell conte

[andrei]

Let's say i have a column with lots of cells . Some containt text , some are
empty . Macro should read all the cells . If the given word/character is
found , it moves the content in B column . B column should not have empty
cells

Example :

Keyword : mother

A1 : mother and father
A2 empty cell
A3 : mother
A4 : son
A5 : empty cell
A6 : mother and nice

The results in B column should be :

B1 : mother and father
B2 : mother
B3 : mother and nice

 

[Mike H]

Hi,

Try this

Sub Versive()
x = 1
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If InStr(UCase(c.Value), "MOTHER") > 0 Then
Cells(x, 2) = c.Value
x = x + 1
End If
Next
End Sub

Mike

 

[andrei]

Many thanks , it works !

Hi,

Try this

Sub Versive()
x = 1
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If InStr(UCase(c.Value), "MOTHER") > 0 Then
Cells(x, 2) = c.Value
x = x + 1
End If
Next
End Sub

Mike

 

[Dave Peterson]

Instr() has its own comparison parm that you can use to ignore case differences:

If InStr(1, c.Value, "MOTHER", vbtextcompare) > 0 Then

 

[Mike H]

Glad I could help and thanks for the feedback

Many thanks , it works !

 

[andrei]

thanks for the tip

Instr() has its own comparison parm that you can use to ignore case differences:

If InStr(1, c.Value, "MOTHER", vbtextcompare) > 0 Then