Find and move text to the A column

  • Thread starter Thread starter nancy
  • Start date Start date
N

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.
 
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.
 
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
 
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
 
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
 
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
 
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


.
 
Back
Top