Find text and move

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

 

[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

 

[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

 

[Johnny]

Thx Guys. that help me a lot.

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

 

[Don Guillett]

The archives would like to see your final solution.

 

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

 

[Don Guillett]

I'm confused

 

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