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

  • Thread starter Thread starter andrei
  • Start date Start date
A

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
 
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
 
Many thanks , it works !

Mike H said:
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
Click to expand...
 
Instr() has its own comparison parm that you can use to ignore case differences:

If InStr(1, c.Value, "MOTHER", vbtextcompare) > 0 Then
 
thanks for the tip

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

If InStr(1, c.Value, "MOTHER", vbtextcompare) > 0 Then
Click to expand...
 
Back
Top