Find text string and move contents to another cell

[Philusofical]

How can I find a specific string in a worksheet and move it to another column?

In Excel 2003, Sheet Range A1:N250
Column "N" of a worksheet "spares" contains text "PRP Pxxxxx" or "Prod
xxxxx" where x is a number. I want to cut and paste anything containing
"PRP" to column Q, and "Prod" to column R, leaving the cell in column N
blank.

Thanks in advance for any advice

 

[Don Guillett]

Look in the vba help index for FINDNEXT

 

[Rick Rothstein]

Give this macro a try...

Sub MovePRPs()
Dim R As Range
On Error GoTo NextSearch
Do
Set R = Columns("N").Find("PRP", LookAt:=xlPart, MatchCase:=False)
R.Offset(, 3).Value = R.Value
R.Clear
Loop
PRODs:
On Error GoTo Done
Do
Set R = Columns("N").Find("PROD", LookAt:=xlPart, MatchCase:=False)
R.Offset(, 4).Value = R.Value
R.Clear
Loop
NextSearch:
Resume PRODs
Done:
End Sub

 

[Steven B]

With a small range of data like that, it's easier to Sort by Column N.
That will group all PRP and Prod together. Select the chunk of data
and paste it where you want it. That should take a minute, rather than
15 minutes to write the script.

If sort order is an issue, add a rowID column at the beginning of your
spreadsheet to return to normal after the sort.


Steven

 

[Rick Rothstein]

This is a little bit less efficient, but more readable, than the other code
I posted... but as Steven pointed out, we are not talking about a lot of
cells here, so the inefficiencies in this code should be well masked...

Sub MovePRPs()
Dim C As Range
Dim X As Long
For X = 1 To 250
Set C = Cells(X, "N")
If C.Value Like "PRP*" Then
C.Offset(, 3).Value = C.Value
C.Clear
ElseIf C.Value Like "PROD*" Then
C.Offset(, 4).Value = C.Value
C.Clear
End If
Next
End Sub

 

[Philusofical]

Many thanks for the code. Much appreciated

This is a little bit less efficient, but more readable, than the other code
I posted... but as Steven pointed out, we are not talking about a lot of
cells here, so the inefficiencies in this code should be well masked...

Sub MovePRPs()
Dim C As Range
Dim X As Long
For X = 1 To 250
Set C = Cells(X, "N")
If C.Value Like "PRP*" Then
C.Offset(, 3).Value = C.Value
C.Clear
ElseIf C.Value Like "PROD*" Then
C.Offset(, 4).Value = C.Value
C.Clear
End If
Next
End Sub