Macro Script to return a cell values realted to a string

I am trying to create a script that will search for a specific string through a massive amount of garbled data and return a specific cell value if it finds a match. It would need to run a continuous loop until it gets to the end of the data. So in the end I get a nice clean list on another tab with all of the values I am looking for.

For example...I want to return all of the "Results" that contain the string "Accept Referred".

Result 1
Accept Referred
John
Smith

Result 2
Accept
Sam
Smith
303 E Washington
Phx
Az

Result 3
Accept Referred
Frank
Smith
303 E Washington

Result 4
Accept
Jim
Smith
Utah


The final results would look like this:
Result 1
Result 3

Please see attached.

Worked on a small scale, but when I added the actual data I am using, it did not work properly. To give you a little background, I basically max out a Sheet with data and there may be 5,000 "Record IDs". There will be garbled data for each record ID that will have addresses, phone numbers, written notes, etc. Some will have the string, "added to accept list" and some will not. I need to have all of the Record IDs that have that string atatched to it. I have attached a better example of the data I have and what I want it to show. I have removed all data and added generic information.

Try

Please Login or Register  to view this content.

I get a "Type" Mismatch" error with "Sub test()" Jindon.

Since I can not download your latest file, I used your first file.

See if it works.

Please Login or Register  to view this content.

From what I can tell, you are using , char (2), to refer to the cell directly above the string, 'Accept Referred". The issue with that is, it's not always in that direct spot. It may be X amount of cells above the String. I should have made my example a little better to show that sorry...For Example:

Result 1
Accept Referred
John
Smith

Result 2
Sam
Smith
303 E Washington
Phx
Az
Accept

Result 3
Frank
Smith
Accept Referred
303 E Washington

Result 4
Utah
Accept
Jim
Smith
Result 1
Accept Referred
John
Smith

Then this should do.

Please Login or Register  to view this content.

That did not work either...

Originally Posted by owensjb2

That did not work either...

This doesn't help at all.

How it is not working?

I could open your last file.
This should do

Please Login or Register  to view this content.

Hmmm I think we are getting close...It worked with the test data but when I put in my actual data I get a "Run-Time error ' 13': Type Mismatch.

The error is on the line:

txt = Join(Application.Transpose(Sheets("raw data").Range("a1") _
.CurrentRegion.Columns(1).Value), vbLf)

Thoughts?

Here is another version of a sample spreadsheet...This may be a better sample of what I have.

Don't wanna work with "better" sample.

I have a lot of black spaces within all of the data, so I tried doing a find/replace for all of the spaces and replaced with no spaces. That helped with the error but now I am not returning any results because the String it's searching for is, .Pattern = "(Record added to )?Accept List" when now the result is, RecordaddedtoAcceptList. I tried .Pattern = "(Recordaddedto)?AcceptList" and got no results....

If you think you can alter the given codes, you should be able to write the whole codes for yourself already.

Any other thoughts on where I'm having my issue?

I think the main issue is that there is something within all of my data (a link, an integer, a function, something) that is causing that line to error. I just don't know what I can do to convert all of the data to acceptable data for the code you have provided.

As I said already, I don't wanna work with what you call "Better" sample anymore.

If the last attached is exactly the same data structure and the key words, I can post the code, otherwise just wasting time for both.

By the way do your country have an appreciation words for those who try to help you?

YIKES!!!!

In my country we help out until an issue is completely solved for those who have questions. At that point a well deserved Thank You would be in order. While I appreciate your help, your ability to just quit while I am still clearly having issues makes all of your hard work worthless to me.

Instead of feeling gratitiude for all you attempted for me, I am now even more frustrated that I have code that almost works, but I can't figure out where my issue is.

So, thank you for taking your time to "help" me with my issue and making it worse.