Search for a word in a worksheet and return row
Closed
tina16marie
Posts
2
Registration date
Monday April 11, 2016
Status
Member
Last seen
May 17, 2016
-
Apr 11, 2016 at 11:56 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 13, 2016 at 06:11 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 13, 2016 at 06:11 AM
Related:
- Search for a word in a worksheet and return row
- How to search for a word on a page - Guide
- Number to text in word - Guide
- Word apk for pc - Download - Word processors
- Insert a tick in word - Guide
- Www.google.com search video - Download - Video playback
2 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 12, 2016 at 01:48 AM
Apr 12, 2016 at 01:48 AM
Hello TinaMarie,
The following code, using autofilter should do the task for you:-
The code uses an Input Box in which you can enter any word to search in Column AG. If any matches are found in Column AG in the sheet1 data set, then the whole relevant row of data is transferred to sheet2.
The code also assumes that there are headings in sheet1, row1.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/sinzs0xxd08ld28/Tina16Marie%28Autofilter%20large%20dataset%29.xlsm?dl=0
Click on the button in sheet2 to open the Input Box and then type in a criteria from sheet1 Column AG (for the sake of the exercise, I have used "Dog", "Cat", "Mouse", "Lion" and "Tiger" as the criteria to search for in Column AG). The Input Box is case sensitive, so type the criteria into the Input Box exactly as it is spelt in Column AG.
My test work book is based on 10K rows of data but I have tested the code on 50K rows and the code still executes in less than a second.
Do you need the "used" data cleared from sheet1 once transferred?
I hope that this helps.
Cheerio,
vcoolio.
The following code, using autofilter should do the task for you:-
Sub TransferData()
Application.ScreenUpdating = False
Dim Srch As String
Srch = InputBox("Please enter the word to search.")
If Srch = vbNullString Then Exit Sub
Sheet1.Range("AG1", Sheet1.Range("AG" & Rows.Count).End(xlUp)).AutoFilter 1, Srch
Sheet1.Range("A2", Sheet1.Range("AG" & Rows.Count).End(xlUp)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Sheet1.[AG1].AutoFilter
Sheet2.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The code uses an Input Box in which you can enter any word to search in Column AG. If any matches are found in Column AG in the sheet1 data set, then the whole relevant row of data is transferred to sheet2.
The code also assumes that there are headings in sheet1, row1.
Following is the link to my test work book for you to peruse:-
https://www.dropbox.com/s/sinzs0xxd08ld28/Tina16Marie%28Autofilter%20large%20dataset%29.xlsm?dl=0
Click on the button in sheet2 to open the Input Box and then type in a criteria from sheet1 Column AG (for the sake of the exercise, I have used "Dog", "Cat", "Mouse", "Lion" and "Tiger" as the criteria to search for in Column AG). The Input Box is case sensitive, so type the criteria into the Input Box exactly as it is spelt in Column AG.
My test work book is based on 10K rows of data but I have tested the code on 50K rows and the code still executes in less than a second.
Do you need the "used" data cleared from sheet1 once transferred?
I hope that this helps.
Cheerio,
vcoolio.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 13, 2016 at 06:11 AM
Apr 13, 2016 at 06:11 AM
Hello TinaMarie,
Is there only one word to be used as the criteria in Column AG?
If so, a Worksheet_Change event could do as you ask. If not, then its back to the Input Box or a search box at least.
Do you mean that there are multiple words in a single cell in Column AG or are you just referring to different words in different cells in Column AG?
To clear things up, it would be good if you could upload a sample of your work book (just a few rows of data, not the whole lot) so we can determine how to go about a solution for you. You can upload a sample to a free file sharing site such as DropBox (as I have done above) and then post the link to your file back here. Be careful with any sensitive data.
Cheerio,
vcoolio.
Is there a way to perform the search without typing the word that I need to manually search. I would like for it to automatically perform the search once I paste the data into Sheet 1.
Is there only one word to be used as the criteria in Column AG?
If so, a Worksheet_Change event could do as you ask. If not, then its back to the Input Box or a search box at least.
Also, my data in AG will have other words in it. For example, I want to be able to search for the word (Dog) in a cell that contain the words (Dog, Cat, Mouse, Lion and Tiger).
Do you mean that there are multiple words in a single cell in Column AG or are you just referring to different words in different cells in Column AG?
To clear things up, it would be good if you could upload a sample of your work book (just a few rows of data, not the whole lot) so we can determine how to go about a solution for you. You can upload a sample to a free file sharing site such as DropBox (as I have done above) and then post the link to your file back here. Be careful with any sensitive data.
Cheerio,
vcoolio.
Apr 12, 2016 at 11:46 AM
Also, my data in AG will have other words in it. For example, I want to be able to search for the word (Dog) in a cell that contain the words (Dog, Cat, Mouse, Lion and Tiger).