Solved Excel search box and copy all results to sheet # 2

March 19, 2014 at 06:23:58
Specs: Windows 7
Good day..

I've searched for an Excel macro that does the following :

1. Press a button to open a search box
2. All results found to be copied to sheet # 2

This would work similarly to a web search engine, where all found results appear. I've seen quite similar examples here in the community, but search parameters must be included beforehand in the macro contents. I need one that is flexible and allows me to use a search box for whatever text string I need to find in each case.

Thanks a lot...


See More: Excel search box and copy all results to sheet # 2

Report •


✔ Best Answer
March 20, 2014 at 14:07:24
I'll let you know right up front that if I were a user, I would not like the way the following macro works, but it does what you've asked. I've look around for a Forms method that accepts both a text input from a user as well as a "radial button" choice, but as I said my Forms skills are minimal.

I'll keep looking...

Option Explicit
Sub FindCopy()
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
Dim tryAgain As Boolean
Dim mySize As String
startSearch:
'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
          GoTo startSearch
     End If
'Set lookat value based on Search String length
    If MsgBox("Exact Match Only? " & vbCrLf & vbCrLf & _
              "Yes For Exact Match Of " & myString & vbCrLf & vbCrLf & _
              "No For Any Match Of " & myString, vbYesNo + vbQuestion) = _
                        vbYes Then mySize = xlWhole Else mySize = xlPart
'Search entire sheet
      With Sheets(1).Cells
        Set c = .Find(myString, LookIn:=xlValues, LookAt:=mySize)
'Perform Copy/Paste/FindNext if myString is found
          If Not c Is Nothing Then
             firstAddress = c.Address
'Clear Sheet2
  Sheets(2).Cells.ClearContents
           Do
'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
               c.EntireRow.Copy _
                 Destination:=Sheets(2).Range("A" & nxtRw)
'Search again
             Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
          Else:
            If MsgBox("Search String Not Found" & vbCrLf & vbCrLf & _
                      "Do You Want To Try Again?", vbYesNo + vbQuestion) = _
                        vbNo Then Exit Sub
                GoTo startSearch
          End If
      End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 19, 2014 at 07:27:54
A few more details would be helpful...

Will the search string always be found in a specific column or does the entire sheet need to be searched?

If the entire sheet needs to be searched, will the search string ever be found more than once in any given row? If so is that a "one find" that needs to be copied or "multiple finds" that all need to be copied?

What exactly needs to be copied: just the found string or the entire row or something else?

When the data is copied to Sheet2, will it repalce the existing data or be appended to the data already in the sheet?

As much specific detail that you can provide would be helpful. If you plan to post any example data, please click on the following statement and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 19, 2014 at 07:48:22
Will the search string always be found in a specific column or does the entire sheet need to be searched?

>> The entire sheet must be searched.

If the entire sheet needs to be searched, will the search string ever be found more than once in any given row? If so is that a "one find" that needs to be copied or "multiple finds" that all need to be copied?

>>Multiple finds that all need to be copied.

What exactly needs to be copied: just the found string or the entire row or something else?

>>The entire row

When the data is copied to Sheet2, will it repalce the existing data or be appended to the data already in the sheet?

>>No, sheet # 2 will be kept blank all the time for copying purposes.

Thanks for your assistance, it is highly appreciated !!


Report •

#3
March 19, 2014 at 08:31:36
This should get you started.

Note: The code assumes that there will always be a value in Column A of the copied/pasted Row. The code uses Column A to determine the next empty Row for the Paste operation in Sheet 2. If Column A of last pasted Row is empty, the code will overwrite that Row. Let me know if that is a problem.

It would help if there was at least one Column that will always have data in it. If not, finding the next empty Row for the Paste is a little more difficult. Difficult, but certainly not impossible.

Option Explicit
Sub FindCopy()
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
'Clear Sheet2
  Sheets(2).Cells.ClearContents
'Get input from user
   myString = Application.InputBox("Enter A Search String")
'Exit if Cancelled
     If myString = False Then Exit Sub
'Search entire sheet
      With Sheets(1).Cells
        Set c = .Find(myString, LookIn:=xlValues, lookat:=xlPart)
'Perform Copy/Paste/FindNext if myString is found
          If Not c Is Nothing Then
             firstAddress = c.Address
           Do
'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
               c.EntireRow.Copy _
                 Destination:=Sheets(2).Range("A" & nxtRw)
'Search again
             Set c = .FindNext(c)
           Loop While Not c Is Nothing And c.Address <> firstAddress
          End If
      End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 19, 2014 at 10:27:44
Fine...let me just request a little addition, please.

1. Could we add the possibility of a warning in case the text string is not found ?

2. If I want to rename Sheet2, do I need to replace "Sheet2" in the script with whatever new name I assign ?

Thanks...I appreciate your advice.


Report •

#5
March 19, 2014 at 12:59:29
Hello again...

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 ?

Please forgive so much nuisance, it's just that the Excel file consists of a product/price list where products carry a two letter symbol, If you try to filter all products bearing the same code, the way it is now Sheet 2 practically shows the complete product list.

Regards.


Report •

#6
March 19, 2014 at 13:28:42
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("Sheet1")
Sheets("Sheet2")
Sheets("Expenses")
Sheets("Search Results")
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.

Sheets("Search Results")

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.

Option Explicit
Sub FindCopy()
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
Dim tryAgain As Boolean
startSearch:
'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
          GoTo startSearch
     End If
'Search entire sheet
      With Sheets(1).Cells
        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
'Clear Sheet2
  Sheets(2).Cells.ClearContents
           Do
'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
               c.EntireRow.Copy _
                 Destination:=Sheets(2).Range("A" & nxtRw)
'Search again
             Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
          Else:
            If MsgBox("Search String Not Found" & vbCrLf & vbCrLf & _
                      "Do You Want To Try Again?", vbYesNo + vbQuestion) = _
                        vbNo Then Exit Sub
                GoTo startSearch
          End If
      End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
March 19, 2014 at 15:47:15
That certainly worked !

Only little detail : I had to add an additional macro button to search and classify products bearing the same 2-letter code. The reason is that in the case of product name search, the option xlPart in the search string solves any combined name description, but when it comes to searching products sharing similar code, is necessary to use the xlWhole option, otherwise a product code like "CM" would show in the results (Sheet2) all product names and descriptions carrying these two letters within their spelling.

Only possible alternative would be creating a message window where you could checkmark partial or whole search, something I donĀ“t kow if it is possible with the macro.

Thanks for your effort and cooperation !!


Report •

#8
March 19, 2014 at 20:50:17
The issue you mention could be dealt with in a few different ways.

Would checking the length of the search string help? xlWhole for 2 letter search strings, xlPart for greater than 2?

Option Explicit
Sub FindCopy()
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
Dim tryAgain As Boolean
Dim mySize As String
startSearch:
'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
          GoTo startSearch
     End If
'Set lookat value based on Search String length
     If Len(myString) > 2 Then mySize = xlPart Else mySize = xlWhole
'Search entire sheet
      With Sheets(1).Cells
        Set c = .Find(myString, LookIn:=xlValues, lookat:=mySize)
'Perform Copy/Paste/FindNext if myString is found
          If Not c Is Nothing Then
             firstAddress = c.Address
'Clear Sheet2
  Sheets(2).Cells.ClearContents
           Do
'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
               c.EntireRow.Copy _
                 Destination:=Sheets(2).Range("A" & nxtRw)
'Search again
             Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
          Else:
            If MsgBox("Search String Not Found" & vbCrLf & vbCrLf & _
                      "Do You Want To Try Again?", vbYesNo + vbQuestion) = _
                        vbNo Then Exit Sub
                GoTo startSearch
          End If
      End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
March 20, 2014 at 06:49:47
This helps, DerbyDad...but I wonder, is there a way that we can create a message window that either asks or provides radio buttons to check, so that the user can independently choose between going "Part" or "Whole" ? This would add flexibility to the script so that it can be adapted to any characteristic of the data profile.

Pardon the insistence my friend !


Report •

#10
March 20, 2014 at 07:01:09
I'm not at a system where I can do any coding right now, but giving the user a choice is doable.

Unfortunately one of the things that I am not skilled at is creating forms with radial buttons. Message Boxes with choices is easy so that's the best I could do.

What exactly are you looking for as far as the wording of the choices and what those choices would look like as far as the search function goes?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
March 20, 2014 at 07:13:33
I appreciate your prompt follow up..

Well, the format or shape does not really matter. Perhaps some message window routine that would ask "Do you want to search for the exact term or word only OR do you want to search for a string containing this term within ?" ( in a sense, something to guide the user as far as selecting the PART or WHOLE option of the macro ).

Thanks...again.


Report •

#12
March 20, 2014 at 14:07:24
✔ Best Answer
I'll let you know right up front that if I were a user, I would not like the way the following macro works, but it does what you've asked. I've look around for a Forms method that accepts both a text input from a user as well as a "radial button" choice, but as I said my Forms skills are minimal.

I'll keep looking...

Option Explicit
Sub FindCopy()
Dim myString, firstAddress As String
Dim nxtRw As Long
Dim c As Range
Dim tryAgain As Boolean
Dim mySize As String
startSearch:
'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
          GoTo startSearch
     End If
'Set lookat value based on Search String length
    If MsgBox("Exact Match Only? " & vbCrLf & vbCrLf & _
              "Yes For Exact Match Of " & myString & vbCrLf & vbCrLf & _
              "No For Any Match Of " & myString, vbYesNo + vbQuestion) = _
                        vbYes Then mySize = xlWhole Else mySize = xlPart
'Search entire sheet
      With Sheets(1).Cells
        Set c = .Find(myString, LookIn:=xlValues, LookAt:=mySize)
'Perform Copy/Paste/FindNext if myString is found
          If Not c Is Nothing Then
             firstAddress = c.Address
'Clear Sheet2
  Sheets(2).Cells.ClearContents
           Do
'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
               c.EntireRow.Copy _
                 Destination:=Sheets(2).Range("A" & nxtRw)
'Search again
             Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
          Else:
            If MsgBox("Search String Not Found" & vbCrLf & vbCrLf & _
                      "Do You Want To Try Again?", vbYesNo + vbQuestion) = _
                        vbNo Then Exit Sub
                GoTo startSearch
          End If
      End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
March 21, 2014 at 12:16:46
Thanks very much, DerbyDad, for your professionalism and solidarity...

Best wishes,

Gus


Report •


Ask Question