Category: excel for developers
Luke Sykes on Tue, 09 May 2017 15:59:42
I have a button in my workbook which loads up a search box
The only problem is, this only takes you one by one to cells containing the data entered, using 'Find next'
The question is...
Is there a macro which I can add to that button which will return all of the detail for that client in separate rows, in the same column format as it is on the monthly finance page on a different worksheet titled 'Client Totals'
Click search button > 'Find What:' - "Steve Smith" [ENTER]
All entries for Steve Smith, anywhere within the workbook are returned on the 'clients totals' sheet in exactly the same layout as it is on the monthly finance page. (That's the only place where the client details are added.
The below example is the clients totals page. It is identical on the Monthly pages.
OssieMac on Tue, 09 May 2017 23:27:30
I believe that it is possible to produce VBA code to find all occurrences of a name as per you requirements but I would need a copy of your workbook with a bit more information of where the name will be found and then want information will be returned and where it is to be returned to.
Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)
- Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
- To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
- Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
- Go to this link. https://onedrive.live.com
- Use the same login Id and Password that you use for this forum.
- Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
- Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
- Right click the file on OneDrive and select Share.
- Select "Get a Link" from the popup menu.
- Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)
Luke Sykes on Wed, 10 May 2017 08:36:35
Below is a link to a blank copy for the workbook
Basically, the VBA code needs to open a 'search' or 'find' dialog box, where a reference number or client name will be entered.
Upon clicking the find button, all records for that client which would be entered on any of the monthly pages throughout the workbook are temporarily duplicated into a identical table on the 'client totals' page. If another clients details are entered in a new search then they are overwritten.
All columns and rows are the same on the clients totals page as they are on the monthly pages.
Many thanks for your assistance
OssieMac on Thu, 11 May 2017 10:17:16
Just a progress report to let you know that I have not forgotten about you. I am working on this but I have had some interruptions due to other things that I have had to do. It is about 8:15pm at the moment in my part of the world and so it will be tomorrow before I resume work on it.
Luke Sykes on Thu, 11 May 2017 16:06:39
Thank you so much for your help.
Terry Xu - MSFT on Fri, 12 May 2017 05:21:43
I think you want to export client data in all monthly finance sheets into Client Totals sheet, right? You could iterate through all sheets and use Find and Copy function to export data.
Here is the example.
Sub Find_Button() Dim str As String Dim lastrow As Integer Dim tlastrow As Integer Dim sht As Worksheet Dim totalSht As Worksheet Dim rng As Range Set totalSht = Sheets("Client Totals") str = Application.InputBox(Prompt:="Seacrh What") For Each sht In ActiveWorkbook.Worksheets If sht.Name = "Client Totals" Then 'skip Client Totals sheet GoTo ContinueLine End If lastrow = sht.Cells(sht.Rows.Count, 3).End(xlUp).Row Set rng = sht.Range("C15:R" & lastrow) 'range for searching With rng Set c = .Find(str, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do tlastrow = totalSht.Cells(totalSht.Rows.Count, 3).End(xlUp).Row sht.Range(c.Address).EntireRow.Copy totalSht.Cells(tlastrow + 1, 1) Set c = .FindNext(c) If c Is Nothing Then Exit Do End If Loop While c.Address <> firstAddress End If End With ContinueLine: Next sht End Sub
OssieMac on Fri, 12 May 2017 12:07:59
Just another update on progress. I think that I have finished the code and so far it appears to be working well. However, I would like to do a little more testing tomorrow before handing it over to you. The finished product is somewhat different to the reply from Terry X but I believe that you will find it somewhat easier to use.
OssieMac on Fri, 12 May 2017 23:35:04
Try the example at the following link.
AFAIK it is not possible to use the "Find dialog" and so I have created a Userform for the purpose of entering the search criteria.
On Client Totals worksheet Click the "Search button" and a Userform will load.
During loading of the Userform, it creates and sorts unique lists of all the numbers and names on the monthly worksheets and populates a ComboBox (DropDown) with the lists. The Unique lists are stored in a worksheet called "Lists" (Last worksheet). This sheet can be hidden if you so desire. I believe that the list of numbers/names is better than attempting to type these into an InputBox because it avoids typing errors.
Use the mouse to click on top bar of Userform and drag the form to where you would like it to be positioned on the worksheet. (On my computer I have placed it over the Search button and just under "Main" and "Totals" buttons). After the first search, Excel will remember the position for the next time you use it and it will automatically open at the same location. This exercise will most likely be required for each different computer that it is used on. The position of the Userform is save in the "Lists" worksheet.
On the Userform, Select Number or Name to Search on. (So the DropDown is populated with the correct list).
The cursor will be positioned in the DropDown. If you start typing in the DropDown then the system will automatically move to matching record in the DropDown list and you can just click it on the list.
Click Go and the data will be populated on the worksheet. When you click Go, the existing data is cleared from the worksheet but you also have a button to manually clear the data at any time.
If you want the Userform in another workbook (This workbook looks like it is only a copy of your Production one) then you can export the Userform and import it to another workbook.
To Export the Userform:
- Open VBA Editor
- Select "SearchForm" in the Project Explorer, left column. (May be necessary to expand the forms tree by clicking on the plus sign so you can see "SearchForm").
- Select Menu item File -> Export file
- In the file dialog, navigate to the folder containing the workbook and then click Save
To Import the Userform:
- Open the required workbook to install the form
- Open the VBA editor
- Select menu item File -> Import file
- Navigate to the folder containing the exported file
- Select the file (will be "Searchform.frm" and click Open.
- Both the Userform and associated code in the Userform's module will be imported.
Code in Worksheet module and Module1
- Copy all of the code in Module1 of the example workbook to Module1 in the required workbook.
- Copy the code in the worksheets module for "Client Totals" to the required workbook. This code removes the Userform when the worksheet is deactivated and this is done in case edits are done on monthly worksheets then the Userform must be re-loaded in "Client Totals" and it will update the DropDown to reflect any modifications.
Hope it all works fine for you and feel free to get back to me with any problems or questions.
Luke Sykes on Tue, 16 May 2017 13:03:33
Thank you both
Excel is such a fantastic product.
Thank you both for your help, you could possibly comprehend how grateful I am!
Many Thanks again
Chenchen Li on Wed, 17 May 2017 09:11:53
If your issue has been resolved, i suggest you mark helpful post as answer to close the thread.
Thanks for your understanding.