Find rows that contain one of 6 variations of specific character string and paste those rows into another worksheet

Sheet 1 contains 54,000 rows of text and/or numbers. I am looking for a macro that will find those rows that contain any of the variations of the 6 RCO strings outlined below and copy those rows that meet this criteria into Sheet 2. The RCO string could be located anywhere within the row.

Variations of RCO string:
"-RCO"
"- RCO"
"– RCO"
"–RCO"
"—RCO"
"— RCO"

Thanks,
Andrea
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
You can achieve this with a user-defined function (UDF) like so:
Function Find_Matches(Test_Cell As Range, Lookup_array As Range) As String
    Dim cl As Object
    For Each cl In Lookup_array
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            Find_Matches = Find_Matches & cl.Value & ", "
        End If
    Next cl
    If Find_Matches <> "" Then
        Find_Matches = Left(Find_Matches, Len(Find_Matches) - 2)
    Else
        Find_Matches = "NOT FOUND"
    End If
End Function

Open in new window


In Excel, you enter this function this way:
=Find_Matches(text_to_search,range_of_values)

For example, if your cell to check is in A2 and you have a list of values to check is on Sheet3 in cells A2:A9 then you'd enter:
=Find_Matches(A2,Sheet3!$A$2:$A$9)

The function will show ALL possible matches in a string, otherwise will return "NOT FOUND" (that result can be changed in the UDF, of course).

You can copy this down, filter out the "NOT FOUND" values, and then copy/paste them to Sheet2.

Regards,
-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
...and here is a macro that will copy all values from Sheet1, Column A that have any of the six strings listed into Sheet2 (starting in row 2):
Option Explicit
Sub Copy_RCO()
    Dim rng As Range
    Dim cl As Object
    Dim x As Integer
    Dim r As Long
    Dim arrRCO() As Variant
    
    arrRCO() = Array("-RCO", "- RCO", "– RCO", "–RCO", "—RCO", "— RCO")

    r = 2
    Sheets("Sheet1").Activate
    Set rng = Range("A2:A" & Cells.SpecialCells(xlLastCell).Row)
    For Each cl In rng
        For x = 0 To UBound(arrRCO())
            If InStr(1, cl.Value, arrRCO(x), vbTextCompare) > 0 Then
                Sheets("Sheet2").Cells(r, 1).Value = cl.Value
                r = r + 1
            End If
        Next x
    Next cl
End Sub

Open in new window


Regards,
-Glenn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Hi Glenn,

Thanks very much for both the UDF and the macro. I had success with the UDF, but the macro is giving me a run-time error '13', Type mismatch, on line 16:

 If InStr(1, cl.Value, arrRCO(x), vbTextCompare) > 0 Then

It would be nice to have the macro option if possible.

Thanks,
Andrea
0
AndreamaryAuthor Commented:
Hi Glenn,

On further investigation, I found I had a few rows that contained "#NAME?" that were causing the problem and preventing the macro from being able to run. Once I deleted those offending rows, the macro worked perfectly.

Thanks so much for the quick and thorough solutions!

Cheers,
Andrea
0
Glenn RayExcel VBA DeveloperCommented:
Glad I was able to help.

Regards,
-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.