Find text string and move contents to another cell
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 |
Find text string and move contents to another cell
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 |
Find text string and move contents to another cell
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 |
Find text string and move contents to another cell
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 |
Find text string and move contents to another cell
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 |
Find text string and move contents to another cell
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 |
All times are GMT +1. The time now is 01:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com