|re: By the way, I notice that if my search string is, for example, a code ("CE") the results will show any row where the words partially contain CE. Is there also a way to search for the exact term all the time? |
I believe that I have fixed that...let me know.
re: 2. If I want to rename Sheet2, do I need to replace "Sheet2" in the script with whatever new name I assign?
The only place I used the string Sheet2 is in the comment lines which start with a single quote. Those are not instructions so you can do whatever you want in those lines and it won't change anything - as long as you don't remove the single quote. You will note that in the actual instructions I used Sheets(1) and Sheets(2). Allow me to explain the difference between Sheet2 and Sheets(2).
VBA can refer to Sheets in a couple of different ways. It can refer to a Sheet by the name on the tab...
Sheets(Sheets("Sheet1").Range("A1")) where A1 on the first sheet contains
the exact name of the sheet you want to refer to.
...or it can refer to a Sheet by it's position in the workbook...
Sheets(2) for the sheet in the second tab position
Sheets(Sheets.Count) for the sheet in the last tab position
Sheets(Sheets.Count - 1) for the sheet in the second to last position
Sheets(Sheets(1).Range("A1")) where A1 on the first sheet contains a number
that represents which sheet you want to refer to.
I could go on with many more examples...
In the code below I used the Sheets(2) syntax so it will always paste the data into the sheet that is in the second tab position, regardless of it's name. If you want to be sure that the data is always pasted into a specific sheet, by name, regardless of it's position, you would change the number inside the parenthesis to match the sheet name, enclosed in quotes. e.g.
If you change the syntax in the code, you should also change the comments just to avoid confusion at a later time.
re: 1. Could we add the possibility of a warning in case the text string is not found ?
I've made a few of changes to the code:
1 - The code will not clear Sheet 2 until the search string is actually found. That way if the user decides to cancel the current search, the data from the previous search will still be there. In addition, if the search string isn't found, the old data will remain.
2 - I added a Message Box that informs the user that the search string was not found and also offers the user the opportunity to try a new search if (s)he so chooses.
3 - I added a test to eliminate a problem if the search box is left empty. If a blank search is attempted, the user will be asked if (s)he wants to try again.
Let me know if you need anything else.
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
Dim tryAgain As Boolean
'Get input from user
myString = Application.InputBox("Enter A Search String")
'Exit if Cancelled
If myString = False Then Exit Sub
'Force valid entry
If myString = "" Then
If MsgBox("The Search Field Can Not Be Left Blank" _
& vbLf & vbLf & "Do You Want To Try Again?", vbYesNo + vbQuestion) = _
vbNo Then Exit Sub
'Search entire sheet
Set c = .Find(myString, LookIn:=xlValues, lookat:=xlWhole)
'Perform Copy/Paste/FindNext if myString is found
If Not c Is Nothing Then
firstAddress = c.Address
'Find next empty Row in Sheet2 Column A
nxtRw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy entire Row to next empty Row in Sheet 2
Destination:=Sheets(2).Range("A" & nxtRw)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
If MsgBox("Search String Not Found" & vbCrLf & vbCrLf & _
"Do You Want To Try Again?", vbYesNo + vbQuestion) = _
vbNo Then Exit Sub
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.