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

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

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

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" wrote:

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

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

Many thanks , it works !

"Mike H" wrote:

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" wrote:

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

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

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

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" wrote:

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



--

Dave Peterson

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

Glad I could help and thanks for the feedback

"andrei" wrote:

Many thanks , it works !

"Mike H" wrote:

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" wrote:

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

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

thanks for the tip

"Dave Peterson" wrote:

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

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" wrote:

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



--

Dave Peterson