Solved

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

Posted on 2014-09-15
5
166 Views
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"
"–RCO"
"—RCO"
"— RCO"

Thanks,
Andrea
0
Comment
Question by:Andreamary
  • 3
  • 2
5 Comments
 
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)
    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
 
LVL 27

Accepted Solution

by:
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
    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
 

Author Comment

by:Andreamary
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.

Thanks,
Andrea
0
 

Author Closing Comment

by:Andreamary
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!

Cheers,
Andrea
0
 
LVL 27

Expert Comment

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

Regards,
-Glenn
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now