Could someone please help me. I have repost this because i
think i orignally posted it on the functions section.

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
-----Original Message-----
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" wrote in
message
...
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.

ymax = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count

xmax = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For x = 1 To xmax
For y = 1 To ymax
If ActiveSheet.Cells(x, y).Value = "John" Then
Cells(x, y).Cut
ActiveSheet.Paste Destination:=Cells(x, 1)
End If
Next y
Next

--
Message posted from http://www.ExcelForum.com