Extract data containing

[Godders199]

Hello, i have written the following which i hoped would extract the relevant rows for me, however it currently returns no results.


Sub Test()
For Each Cell In Sheets("submission report").Range("bd:bm")
If Cell.Value = Sheets("instructions").Range("a8") Then
matchRow = Cell.Row
Rows(matchRow & ":" & matchRow).Select
Selection.Copy
Sheets("Results").Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets("Instructions").Select
End If
Next
End Sub

Effectively i want it to look at columns BD:BM, if the word contained in "Sheets("instructions").Range("a8")" then copy and paste the row to the results tab.

Think the issue might be that the cells contain more than one word , so it is currently only looking for an extact match. I have tried putting ** around A8 but this still does not make any difference.

Grateful if someone can tell me what i am doing wrong.

 

[My Aswer Is This]

You said:

Range("bd:bm")

Do you really need to check that many cells.
That's 10 columns which each have 1.5 million rows?
​

 

[Godders199]

the work sheet normally contains 2000 rows so i can restrict it to look just at the first 2000 rows,

 

[My Aswer Is This]

Try this:
I assume your sheet is named
Instructions
Please check script for proper sheet names.
Capitalization is important.
One place you said instructions another place you said Instructions
Same with all sheet names

Sub Find_Me()
'Modified 3-5-18 6:31 AM EST
Dim SearchString As String
Dim SearchRange As Range
SearchString = Sheets("Instructions").Range("a8").Value
Set SearchRange = Sheets("submission report").Range("bd:bm").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Was not found": Exit Sub
Sheets("submission report").Rows(ans).Copy Sheets("Results").Rows(ans)
Sheets("Instructions").Select
End Sub