Find text and move

J

Johnny

need a formula or macro to find text in columns and move that to the frist
column of row. sample the text(apple) in the column M58 or K90. want to move
to A58 or A90 by formula or macro.
 
D

Don Guillett

One or many?
Look in the vba help index for FINDNEXT.

Sub findandmovetext()
what = "apple"
On Error Resume Next
With Sheets("sheet32").Range("k1:m41")
Set c = .Find(what, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do

Cells(c.Row, 1) = c
c.ClearContents

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub
 
J

Joel

The code below will move data in selected column to column A.

Sub movetext()

ThisCol = ActiveCell.Column
LastRow = Cells(Rows.Count, ThisCol).End(xlUp).Row
For RowCount = 1 To LastRow
If Cells(RowCount, ThisCol) <> "" Then
Cells(RowCount, "A") = Cells(RowCount, ThisCol)
End If
Next RowCount
End Sub
 
J

Johnny

Thx Guys. that help me a lot.

Joel said:
The code below will move data in selected column to column A.

Sub movetext()

ThisCol = ActiveCell.Column
LastRow = Cells(Rows.Count, ThisCol).End(xlUp).Row
For RowCount = 1 To LastRow
If Cells(RowCount, ThisCol) <> "" Then
Cells(RowCount, "A") = Cells(RowCount, ThisCol)
End If
Next RowCount
End Sub
Click to expand...
 
J

Johnny

Hi Don,
this is only can move the selected text. that I want is check the sheet and
find text and move that row to somewhere I wanted. like this, the text in
F58, I want to move from F1-F58 to M58 or N1 by a macro.
 
J

Johnny

ok, in my sheet it has information in row A15,B15,C15,D15, and E15. example
in the box E15 is text(fish), I want a macro to find in cells (fish) and move
from (A15-E15) information to M15 or anywhere I want to. (like K1 or H2). so
all the information A15-E15 will show on M15-Q15, or K1-P1. why I need a
macro is, because every time I open a job, the information that I need is not
in the same rows, the only same is can find the text(fish) in a columns. is
that cleared.
 
Top