Two days ago, a friend asked me, there is no way to implement a table in Excel dropdowns is the original data, in another form to display the search lines, search keywords can be separated, and the search history recorded?
I think, with VBA implementation can certainly ah, but I was thinking, there is no possibility of Excel itself can be implemented, but later did not find that Excel comes with this feature. So think of yourself using VBA to achieve it.
So I opened my computer, and found that my version of Office is WPS, there is no VBA function, online said to use the VBA module installation, but never installed successfully. Finally gave up, self-download a office2013, with VBA features.
In fact, the implementation of the search function is quite simple, nothing more than a few loops, the keywords are divided out of the loop, by the row search cycle, column search cycle, and then get the results, fill the results, for the history, you need to get the last line of the row number.
Attachments uploaded, click to download: cross-form Search example -2003.zip
Http://files.cnblogs.com/files/yougewe/%E8%B7%A8%E8%A1%A8%E5%8D%95%E6%90%9C%E7%B4%A2%E7%A4%BA%E4%BE%8B-2003.zip
Key function code hints:
Sheets ("Raw Data"). Range ("A6"). CurrentRegion'Get selection Area dataSearcharr =Split(Searchstr,"~")'Segmentation KeywordsRange ("a9:v"& Rows.Count). ClearContents'empty the original dataSheets ("Search Records"). Range ("A65536"). End (Xlup). Row'get the line number of the largest rowSheets ("Search Records"). Range ("A"& MaxRow). Resize (m, columnCount) = Brr'Data Population
The implementation code is as follows:
SubClick Search raw Data ()DimI&, J&, m&, c%, t$, ColumnCountDimArr, BRR (), Searcharr () as String, Checkedrow () searchstr=InputBox("Please enter the keywords you want to search, multiple keywords are separated by ~","Search Data Options","Cloud ~ Meal") IfSearchstr ="" Then MsgBox("no search str ...") Exit Sub End IfSearcharr=Split(Searchstr,"~") Arr= Sheets ("Raw Data"). Range ("A6"). CurrentRegion ColumnCount=UBound(Arr,2) rowcounts=UBound(arr) Searcharrcount=UBound(Searcharr)ReDimBrr1 to UBound(arr),0 tocolumnCount)ReDimCheckedrow (1 torowcounts) Range ("a9:v"&rows.count). ClearContents Startrownum=6 fori = Startrownum torowcountsIf(Checkedrow (i) <>1) Then 'because when the results are searched, the whole line is displayed, so as long as you search a row, you can no longer search for the row, avoid duplication, and improve efficiency. forIcolumnnum =1 toColumnCount findStr=0 forIsearchnum =0 toSearcharrcountIfArr (i, icolumnnum) like"*"& Searcharr (Isearchnum) &"*" Thenm= m +1Checkedrow (i)=1findStr=1 Exit for End If Next IfFINDSTR =1 Then forj =0 toColumnCount-1Brr (M, j)= Arr (i, J +1)'populating data by rows Next End If Next End If NextMaxRow= Sheets ("Search Records"). Range ("A65536"). End (Xlup). Row +3 'find the maximum number of rows IfM >0 Thencc=UBound(BRR,1) CC2=UBound(BRR,2) Sheets ("Search"). Range ("A9"). Resize (m, columnCount) =BRR Sheets ("Search Records"). Cells (MaxRow-1,1) ="this search:"& Searchstr &"Search Time:"&Now () Sheets ("Search Records"). Range ("A"& MaxRow). Resize (m, columnCount) =BRRElseSheets ("Search Records"). Cells (MaxRow-1,1) ="this search:"& Searchstr &"Search Time:"&Now () Sheets ("Search Records"). Cells (MaxRow,1) ="no results found" End If 'record operations, whether or not you have any resultsEnd Sub
Note: After using office2013 editing to generate a VBA program, save to 2013 format, the next time you open the macro code is lost, this is a brutal fact. The workaround is: Save to 2003 format.
VB as a scripting language, has its certain characteristics, but do some small functions can be useful.
Excel VBA Implementation cross-form (sheet) search-show search rows record search history