MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find and Move a specific cell

Posted by Sebastien on September 28, 2001 6:11 AM

I'm importing data from a text file, with no way to filter it correctly.
I need to find a specific word and then to move the corresponding cell upper...
I'm able to do this action for the first occurence, I need that the VBA macro do all the sheet ! Please Help Me !!!
I've tried a lot of way (to filter, data sort), but no way.

Here is my code :
For i = 1 To 145
Cells.Find(what:="*ms", lookat:=xlPart, searchorder:=xlByRows, MatchCase:=False).Activate
ActiveCell.Offset(-4, 1).Activate
Next i
When macro tries to find the next occurence, it find the same cell, and so on...

Thanks in advance for your help
Best Regards

Posted by Juan Pablo on September 28, 2001 6:24 AM

I haven't tested this, but i think it'll work for you.

Sub copypaste()
Dim F as Long

For F = 1 to 145 'I assumed this are Rows, right ?
If Cells(F,5) like "*ms" then 'Assumed checking Column E, if not, change 5 to corresponding number A=1, B=2, etc.
Cells(F-4,5+1) = Cells(F,5) 'Offset(-4,1)
Cells(F,5) = ""
End If
Next F

End Sub

Juan Pablo