Find and move text to the A column

[nancy]

I believe this is possible but I am having difficult since
I am new to VB. I have a work sheet with column A TO F. I
will like a code that will search for the word "John" from
any of the column B TO F and move it to column A. The cell
in A to move this "john" will always be blank. It should
move it across the same row. Example.
A B C D E F
70 25 JOHN 5 10
5 JOHN 2 11 5
The macro should be able to move all john to column A.
Answer:
A B C D E F
JOHN 70 25 5 10
JOHN 5 2 11 5

Any help will be grateful.

 

[Ken Wright]

If I read this right and there is no data in Column A at all, then no need for
VBA:-

Assuming your data starts in row2, in cell A2 put the following formula and copy
down:-

=IF(COUNTIF($B2:$F2,"*JOHN*")>=1,"JOHN","")

Now select Col A and do Edit / Copy then Edit / Paste Special / Values.

Now select cols B:F and do Edit / Replace - Find = "JOHN" / Replace with = Leave
Blank - Hit OK, Done.

 

[nancy]

Thanks for the help. There are some data in cell A. but
not in the cell that john will be moved to. Sorry about
the confusion. Also, there is a macro that this macro will
call after it has moved john. Any help. Thanks in advance

-----Original Message-----
If I read this right and there is no data in Column A at all, then no need for
VBA:-

Assuming your data starts in row2, in cell A2 put the following formula and copy
down:-

=IF(COUNTIF($B2:$F2,"*JOHN*")>=1,"JOHN","")

Now select Col A and do Edit / Copy then Edit / Paste Special / Values.

Now select cols B:F and do Edit / Replace - Find

= "JOHN" / Replace with = Leave

 

[Cecilkumara Fernando]

Nancy,
assumed that you have 5 columns of data to start with (A-E)
this macro first move the "John"s to columnF
then insert a columnA, cut "John"s from columnG and paste it in columnA
Select the entire table and run this

Sub Macro1()

Lrow = Selection.Rows.Count
Selection.Find(What:="JOHN", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
fcell = ActiveCell.Address
Do
Selection.FindNext(After:=ActiveCell).Activate
Cells(ActiveCell.Row, 6).Value = ActiveCell.Value
ActiveCell.ClearContents
ccell = ActiveCell.Address
Loop Until ccell = fcell
Range("A1").Select
Selection.EntireColumn.Insert
Range("G1:G" & Lrow).Cut Destination:=Range("A1")

End Sub

HTH
Cecil

 

[nancy]

Thanks cecil. but i am getting runtime error 448. There is
already a column A. I want it to search all current
columns and cut and paste the name john to the A cell
across from the column where it was at original..
Thanks again

 

[Alan Beban]

Sub testit2()
On Error Resume Next
Dim rng As Range, i As Long
Set rng = Range("A1:F100").Rows
For i = 1 To rng.Count
rng(i).Find("JOHN", , , xlWhole).Cut rng(i).Cells(1)
Next
End Sub

Change the 100 to suit your data.

Alan Beban

 

[nany]

EXCELLENT ALAN. Thanks a million. you make my day.

-----Original Message-----
Sub testit2()
On Error Resume Next
Dim rng As Range, i As Long
Set rng = Range("A1:F100").Rows
For i = 1 To rng.Count
rng(i).Find("JOHN", , , xlWhole).Cut rng(i).Cells(1)
Next
End Sub

Change the 100 to suit your data.

Alan Beban


.