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
167 Views
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
Question by:Andreamary
• 3
• 2

LVL 27

Assisted Solution

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
End If
End Function
``````

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

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
``````

Regards,
-Glenn
0

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.

Thanks,
Andrea
0

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!

Cheers,
Andrea
0

LVL 27

Expert Comment

ID: 40324031
Glad I was able to help.

Regards,
-Glenn
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

iPhone excel activation issues 11 66
highlight duplicate entry 16 27
macro for each dropdown 15 44
InStr Function not working properly in macro 3 19
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.