Sorry if this questions has been asked recently. I have searched for an
answer to no avail.

I'm trying to write a macro that will search an excel spreadsheet for exact
text. Once if finds the exact text match(es)
it should then assign a value to a cell in the same row (maybe yes or no)
then it should sort the list by rows that match delete the rest.

This is not too bad to do but I highly recommend not deleting the extra
lines. Once deleted they are gone never to return. I promise you that at some
point something will happend and the wrong stuff will be deleted and there
will be no way back. Or someone will swear that it deleted the wrong thing
and you will have no way to prove that the item never existed. I recommend
leaving the source data alone and copying out the found data to a new sheet.
Something like this...(This sub looks for the word "This" in Sheet1, Column
A...

Public Sub CopyFoundStuff()
Dim wksCopyTo As Worksheet
Dim wksCopyFrom As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wksCopyFrom = Sheets("Sheet1")
Set rngToSearch = wksCopyFrom.Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry ""This"" was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
Set wksCopyTo = Worksheets.Add
rngFoundAll.EntireRow.Copy wksCopyTo.Range("A2")
End If

End Sub

--
HTH...

Jim Thomlinson


"John Hughes" wrote:

Sorry if this questions has been asked recently. I have searched for an
answer to no avail.

I'm trying to write a macro that will search an excel spreadsheet for exact
text. Once if finds the exact text match(es)
it should then assign a value to a cell in the same row (maybe yes or no)
then it should sort the list by rows that match delete the rest.

Thanks for your reply Jim. I'm still a bit lost.
I realize that I need to study macros a bit more. I did post this question
in the programming group. Can you provide me a list of changes/next steps in
order for me to use this Sub?

I know I need to change the column, "this" ( how do I list and seperate the
dozen strings of text?)

what else?



"Jim Thomlinson" wrote:

This is not too bad to do but I highly recommend not deleting the extra
lines. Once deleted they are gone never to return. I promise you that at some
point something will happend and the wrong stuff will be deleted and there
will be no way back. Or someone will swear that it deleted the wrong thing
and you will have no way to prove that the item never existed. I recommend
leaving the source data alone and copying out the found data to a new sheet.
Something like this...(This sub looks for the word "This" in Sheet1, Column
A...

Public Sub CopyFoundStuff()
Dim wksCopyTo As Worksheet
Dim wksCopyFrom As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wksCopyFrom = Sheets("Sheet1")
Set rngToSearch = wksCopyFrom.Columns("A")
Set rngFound = rngToSearch.Find(What:="This", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry ""This"" was not found."
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
Set wksCopyTo = Worksheets.Add
rngFoundAll.EntireRow.Copy wksCopyTo.Range("A2")
End If

End Sub

--
HTH...

Jim Thomlinson


"John Hughes" wrote:

Sorry if this questions has been asked recently. I have searched for an
answer to no avail.

I'm trying to write a macro that will search an excel spreadsheet for exact
text. Once if finds the exact text match(es)
it should then assign a value to a cell in the same row (maybe yes or no)
then it should sort the list by rows that match delete the rest.