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

Posted on 2014-09-15
Last Modified: 2014-09-15
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"

Question by:Andreamary
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
ID: 40323695
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)
        Find_Matches = "NOT FOUND"
    End If
End Function

Open in new window

In Excel, you enter this function this way:

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:

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.

LVL 27

Accepted Solution

Glenn Ray earned 500 total points
ID: 40323781
...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
    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


Author Comment

ID: 40323933
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.


Author Closing Comment

ID: 40323947
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!

LVL 27

Expert Comment

by:Glenn Ray
ID: 40324031
Glad I was able to help.


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question