Excel VBA Implementation cross-form (sheet) search-show search rows record search history

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&amp, 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