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

Look in the vba help index for FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

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

--
Rick (MVP - Excel)


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

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


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

Many thanks for the code. Much appreciated

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


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